Drop down menu in excell

[mtevensorrison]

Hello

Ive been given a wee job by my gaffer which includes the following!

i will create a drop down list from 1 - 10

when a number is selected, the cell next to it will be filled by text which is linked to the chosen number!

there may also be some calculations to be performed in the cells next to the text cells also!

does anyone have any ideas??

regards in adavance

 

[randy700]

Ideas about which part?

How to create the dropdown?
How to fill the cell?
Or, how to construct the formulae?


Randy

 

[mtevensorrison]

Sorry, im not the best at giving detailed explanations!

i know how to create the drop down menu, i also know how to create formulas, i jst need to know how to link a drop down down menu to the cells next to it?

Hoep this explains it a bit better!

thanks again

 

[xlbo]

Use Data Validation for the dropdown
Use a VLOOKUP formula to match the selection from the dropdown list to the lookup table which holds the numbers and text

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

Thanks for the reply Geoff

This Vlookup, is this a complicated process or relatively easy to use!

will i find it i excel

regards

steven

 

[xlbo]

vlookup is a formula in excel. You would put htis in the cell next to the data validation dropdown. View the help file for full details of the syntax. Alternatively, search this forum for multiple examples of using vlookup

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

Thanks for all the help people

finally got my template working!

 

[mtevensorrison]

Come to think of it, im in trouble again!

Ive managed to link 2 rows in sheet 2 to the the chosen option from the list in sheet 1!

can anyone please tell me how i would link more then 2 collums, i need to add at least 5 collums to the chosen value in the drop down list!

Thanks in advance

 

[xlbo]

rows or columns ? you have just said both - show us what you have and what you need - we ain't mind readers.......

the vlookup formula can be used in as many cells as you want to return data linkied to the chosen option

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

I solved it

thanks anyway

all i had to do was replace the number in the formula

=VLOOKUP(A1,Price,2,FALSE)

so the 2 represents the collumn which will be displayed

thanks

 

[mtevensorrison]

Hi again

Ive come across a little problem with my list + vlookup spreadsheet

im creating this worksheet for staff in my workplace to select option which have certain values assigned to them in other cells!

but heres my problem which im sure is simple enough!

i want the work sheet to be blank when it is opened

but i cant seem to delete the contents of the cells without deleting the vlookup formula!

does anyone out there have a solution to this problem!

regards

Stevie

 

[xlbo]

You can't delete the contents of a cell without deleting its formula. To not display data, amend your formula thus:

=if(A1="","",VLOOKUP(A1,Price,2,FALSE))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

Thanks for that

but now my list returns nothing, so im kinda back to square 1

is there any way of getting =if(A1="","",VLOOKUP(A1,Price,2,FALSE))

to display data once a number is selected from the list!

Thanks again

 

[xlbo]

That kinda depends if the list selection is in A1

That formula will perform a lookup when cell A1 has data in it. When there is no data it will not display anything.

What cell is your dropdown list in ? I copied your formula that you posted so assumed your data being looked up was in A1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

Yeah my list starts in A1 but it goes down to A9

thanks

 

[xlbo]

my list starts in A1 but it goes down to A9

I asked about your list SELECTION

Where does the DROPDOWN box where you choose your value reside ? THAT is the cell you need to reference in your VLOOKUP formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

Sorry Geoff

My data resides in b2 - b10, J2 - J10, K2 - K10!

ive got a short fix the now but any proper solution would be welcome!

thanks

 

[mtevensorrison]

its also in sheet 2 of the workbook which is named data!

 

[xlbo]

????? I have no idea what you are talking about any more

The vlookup formula returns the correct lookup value? yes/no

If you make the amendment to the formula as I have shown you, it will return "" when there is no value in the referenced cell. It will return the lookup value when there is data in the referenced cell

I really don't understand what the question is that you are asking now ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mtevensorrison]

I've tested this and it returns "" even when there is data in the referenced Cell!

i seem to have found a way around it though!

i have another question if you think you can bear any more of my terrible descriptions!

right now i have one drop down list which links to 3 collumns of data on the sheet2 of the work book!

if i wanted to add another list below the one i already have, would the data im referencing for my new list have to be on another sheet as using the same sheet doesn't seem to work, it just displays the formula - - =Vlookup(A27,additional,2,false)

Thanks Again