Linking drop down list with data (Easy!) 3

[AllUserNamesAreTaken]

Well, easy for some maybe but I just can't seem to get it working.


I want to link a drop down list with data. EASY.

On the drop down list (already created) i've got say:

1
2
3
4
5

and I want the next cell on the right to link in with that.

So if "1" was selected then you'd get: "Monday"
So if "2" was selected then you'd get: "Tuesday"

etc.

Not difficult, just I can't seem to get my head around it.

Anyone?

 

[lupins46]

?Software?

 

[AllUserNamesAreTaken]

Excel - Sorry.

 

[xlbo]

Rather than giving an approximation of what you have, why not tell us EXACTLY what you have - it will make a difference as to which (of several) responses I can give. there are MANY ways of doing this but the best one depends on EXACTLY what you want to do...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AllUserNamesAreTaken]

Okay.

In cell C14 I have a drop down list using Validation.

The options are:

"300 x 90"
"200 x 75"
"150 x 75"
"100 x 50"

In Cell E14 I want it to display the prices of each.

So for 300 x 90 I'd like it to display £33.00

... 200 x 75 £22.00
... 150 x 75 £18.00
... 100 x 50 £15.00

If you can help that'd be greaqt.

 

[xlbo]

if you only have 4 options then the easiest formula is:

=CHOOSE(MATCH(D1,A1:A4,0),33,33,18,15)

where you have your dropdown cell in D1 and your list of values in A1:A4

If you have more values, you would be better off using VLOOKUP or INDEX/MATCH

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AllUserNamesAreTaken]

Can't seem to get it to work... I'm so confused with this.

 

[xlbo]

what have you tried ??
what formulae and lists for the validation have you got set up ??

You need to provide better info and some indication that you are trying things out...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AllUserNamesAreTaken]

Okay..

In Cell C14 I've got a drop-down list that gets the data from cells: AZ9:AZ12

The data is:

300 x 90
200 x 75
150 x 75
100 x 50

I want Cell E14 to link to them. So for example, if I selected "300 x 90" from the drop down list it would return a fixed price of $33.00

Same for 200 x 75 but $22.00 instead.

They're always a fixed price

 

[DPlank]

If the price is to appear in one cell only, you can simply nest an IF statement like this:

=if(C14="300 x 90", 33, IF(C14="200 x 75", 22, IF(C14="150 x 75", 18, IF(C14="100 x 50", 15))))

Not the most futureproofed way of doing it but it'll get you what you want for now.

Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[DPlank]

The last statement in my formula:

IF(C14="100 x 50", 15))))

Oops.

Should read

IF(C14="100 x 50", 15, 0))))

Sorry about that.
Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[AllUserNamesAreTaken]

Cheers mate. Works well.

That's all I was after really - just a simple solution.

Thanks.

 

[xlbo]

This forum (and whole site) is not about "simple solutions" - it is NOT a help desk - it is about learning and development (although a simple solution that works is preferable to a complex one ;-) ).

If all you want is an easy answer, may I suggest you try a different site. If you want to actually LEARN then by all means stick around

Given your data set, then:
=CHOOSE(MATCH(14,AZ9:AZ12,0),33,33,18,15)

will work - however, it is not very future proof either - for 1 you can NAME your list that you want to get the validation from - that way you can have it on a different sheet - it will also make expanding the set of values easier:
=CHOOSE(MATCH(14,myList,0),33,33,18,15)

that way, if you want to add entries to the list, all you need to do is update the name in the NAME DEFINITION box. You can even create DYNAMIC NAMES which calculate the number of rows of data and expand / contract the range accordingly - there is a nice FAQ by Skipvought on this particular subject...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

This forum (and whole site) is not about "simple solutions" - it is NOT a help desk - it is about learning and development (although a simple solution that works is preferable to a complex one ;-) ).

If all you want is an easy answer, may I suggest you try a different site. If you want to actually LEARN then by all means stick around

Given your data set, then:
=CHOOSE(MATCH(14,AZ9:AZ12,0),33,33,18,15)

will work - however, it is not very future proof either - for 1 you can NAME your list that you want to get the validation from - that way you can have it on a different sheet - it will also make expanding the set of values easier:
=CHOOSE(MATCH(C14,myList,0),33,33,18,15)

that way, if you want to add entries to the list, all you need to do is update the name in the NAME DEFINITION box. You can even create DYNAMIC NAMES which calculate the number of rows of data and expand / contract the range accordingly - there is a nice FAQ by Skipvought on this particular subject...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

apologies for the double post - annoying timeouts - this is the formula that should work:

=CHOOSE(MATCH(C14,AZ9:AZ12,0),33,33,18,15)

=CHOOSE(MATCH(C14,myList,0),33,33,18,15)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AllUserNamesAreTaken]

By all means I am willing to learn.

So, how do you name a set of cells then?

 

[xlbo]

to NAME a set of cells, go

Insert>Name>Define

Enter a name in the top text box and define the range by typing or using the mouse to select the cells

in help, the keywords are: Range Name

This will allow your data validation lists to be kept on a seperate sheet....there are many other uses as well but this is the one pertinent to your current situation...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AllUserNamesAreTaken]

Thanks for all your help guys, you've all been really cool.

I do realise this is not a helpdesk, I just get stressed at work sometimes!

Another question about excel: How do you set the row height for all rows? Not just one at a time. Thanks.

 

[AllUserNamesAreTaken]

Forget it, that was a stupid question.

If I've got any more problems I'll let you know.

 

[DaleWatson123321]

1) Click on the space at the top-left-corner of the worksheet - to the left of the "A" for the first column, and above the "1" for the first row. This will select the ENTIRE sheet.

2) With the entire sheet selected, now right-click anywhere on the selected area.

3) Select "Row Height" from the dropdown list, and specify your desired height.

Regards, Dale Watson