Excel Question - Creating an automatic value entry based on the text in another cell.

[mankymummy]

eg. if the text in cell A1 is BREAD then the value in A2 is automatically 23p, if it is CHEESE then its 54p etc. etc. for up to maybe 30 items.

is that possible? thank you !

you probably want to use VLOOKUP:

create a table in A1:B30, so A1 is Bread, B1 is 23p, and so on.

Then =VLOOKUP("Bread",$A$1:$B$30,2,FALSE)

If you have any more Excel questions, try www.mrexcel.com - an incredibly helpful forum.

You can create a look-up table on another worksheet. So it will read bread and put in the value etc.

If you don't have a manual then the help menu could prove useful.

Hope this helps

the IF function will only work for 7 different items
give me a minute

cornflakegirl is quicker at typing!

ah yes, vlookup was the one I was thinking of

Um, actually, just realised that you wanted the input / output in A1 and A2 - so don't put the table there!

If you put the table in C1:D30, and the value you want to look up is in A1 then

Then =VLOOKUP(A1,$C$1:$D$30,2,FALSE)

(Apologies if that is obvious, don't know what your level of Excel skill is.)

(I always put my table on a separate sheet so I can hide it)

thanks that looks great. will give it a go. no apologies needed, i obviously dont know what i'm doing as am posting here!!!!

done it. you are genius's. thanks a load.

(To be really professional, put the table on a separate sheet, make it a named range, refer to the range in the VLOOKUP, hide the sheet.)

Of course, if you try to get VLOOKUP to return an answer for something that's not in the table, it'll give you an error message. You could use data validation to create a drop-down in A1 so that only values in the table can be selected...

(Can you tell that I like Excel a bit too much? )

managed to put the table on a separate sheet...[proud emoticon]

like the idea of a drop down... how would i do that? could i type something thats not in the lookup table for a one-off if i have a drop down though?

i think you're brilliant, im glad you like excel so much. you've saved me about a weeks faffing around already!!!!

Okay, if your table is on another sheet, you will need to use a named range to do a dropdown. Very easy - just go to the sheet, select all of the names (eg A1:A30), then click into the Name Box (just above the A column header) and type Food (or whatever other name you want).

Then, click into your other sheet, and select cell A1 (or wherever you want the dropdown). Then go to the Data menu and select Validation. In the Allow box select List and in the Source box type =Food.

Et voila, a dropdown list!

You can't type in a one-off with a drop down list - but your VLOOKUP wouldn't work anyway if the value wasn't in the table. If you think you may want to add things to your table (and your dropdown), then just leave some blank lines at the bottom of the range that you use for the VLOOKUP and the same number of blank lines when you set up your named range.

(You can also create dynamic named ranges, that change size automatically when you add more data, but that's probably overkill here! If you want to know how to do it, do a quick search for dynamic range at mrexcel.com, or PM me there - same username).

fabulous. thank you so much. and for explaining so clearly that even I managed to make it work !

glad to help