Excel drop downs

[ovenpaa]

I am helping the eldest with his homework - he has to create a xls spreadsheet for doing heat loss calculations which we have now done. Part of the sheet needs a variety of 'U values' inputting so I thought it would be nice to do this with a drop down - and it is, however I can only input the value but not the name like this:

1.0
2.2
etc.

What we really want is this:

Internal wall
Flat roof
etc

Or alternatively:

1.0 Internal wall
2.2 Flat roof

BTW the above values are just examples for all you Heating types!

Anyways, how can I create a drop down table that has a description in it and either has a number in at as well that I can use directly for a simple calculation, or a description that references a number so I can use that for a calculation.

Does that makes sense? Yes spreadsheets are not my strong point!

TIA.

 

[Deleted account 220427002]

What ever happened to richard topping and his great computer guides on TV when you need him ( that's the box in the corner with the changing lights Tarka)

 

[redcastle]

Not with you at all really but you can insert a mouse over.
so you input the data. e.g 2.2.
Now right click and choose "insert comment" type in whatever you want.
now when you put your mouse over the value (2.2) it shows the comment as below.
any good to you ?????

 

[Wizard]

Short of writing a full-blown macro, the easiest option would be to create your list in cell A1, create an if/then/else in cell B1 to select a value according to what is selected in A1, and then in cell C1 do your calculation relating back to cell B1.

I'll have a play and come up with a wee example if you need me to.

 

[ovenpaa]

Not with you at all really but you can insert a mouse over.
so you input the data. e.g 2.2.
Now right click and choose "insert comment" type in whatever you want.
now when you put your mouse over the value (2.2) it shows the comment as below.
any good to you ?????

That us nice - can I do that on a drop down table? I assume I put the comments on the original table I validate? - Hard to check right now as I am back on Suse now.

 

[ovenpaa]

Short of writing a full-blown macro, the easiest option would be to create your list in cell A1, create an if/then/else in cell B1 to select a value according to what is selected in A1, and then in cell C1 do your calculation relating back to cell B1.

I'll have a play and come up with a wee example if you need me to.

Yes a small example would be handy! - I really dont do spreadsheets to this level despite using them quite often.

 

[Wizard]

PM on it's way

 

[Dellis]

type the values you want to see in the drop down in a seperate lis (should be somewhere out of sight or hide the list) select the cell you want to be able to use the driop down from then from the menu items at the top of the sheet select Date, validate, a window will open and in the top box select list, the select the location of your list in the source box. See the attached example - select cell A1 the "data", "validate" and all will be revealed.

The example helps my poor explanation.

HTH
Dave

 

[Wizard]

Attach a file. Why didn't I think of that

If you look at this and the Excel help, all will become clear.

Cell A1 contains your list, which is defined on Worksheet 2. You can define your list however it suits you to, that doesn't matter for the rest of it.

Cell B1 is blank to allow for the drop-down arrow, you can re-format to suit.

Cell C1 contains a "nested IF statement". A basic If statement is:
=IF (Location=Condition, Display Value if TRUE, Display Value if FALSE)

A nested IF is:
=IF (Location=Condition, Display Value if TRUE, IF(Location=Condition, Display Value if TRUE, Display Value if FALSE))

Where you replace the "FALSE" with another IF. And you keep doing that for each value in your list.

=IF (Location=Condition, Display Value if TRUE, IF(Location=Condition, Display Value if TRUE, IF(Location=Condition, Display Value if TRUE, Display Value if FALSE)))

It can get confusing if you're sloppy, but if you use the example I've included and just add one extra IF to the "Select Item" for each entry on your list, it'll be fine

 

[Zippy]

type the values you want to see in the drop down in a seperate lis (should be somewhere out of sight or hide the list) select the cell you want to be able to use the driop down from then from the menu items at the top of the sheet select Date, validate, a window will open and in the top box select list, the select the location of your list in the source box. See the attached example - select cell A1 the "data", "validate" and all will be revealed.

The example helps my poor explanation.

HTH
Dave

To build on Dave's suggestion and an alternative approach to Wizard's:

Type the 'u' value alongside the list area so you have a nice little table;

Then in column C use the vlookup command to look up the 'u' value from the table according to the drop down item selected:
e.g. =VLOOKUP(A1,D6:E10,2,False)
In the above, A1 refers to the drop-down cell, D6:E10 refers to your table of 'u' values, 2 indicates to return the 2nd column from the 'D6:E10' table and 'False' ensures an exact match is returned for the lookup value.
Once you get the hang, then I think its a bit easier than nested IFs.

The returned value in column C can be used just as normal, it'll change as soon as the drop-down value is changed.
You can even 'embed' the lookup function into your u-value calculation if your want.


I'll try attaching the file....(first time so be patient!)

Let me know if the above doesn't make sense.

Steve

 

[Wizard]

use the vlookup command

Actually clean forgot about that one

 

[(RIP) Kaister]

whats wrong with graph paper - thats all we ever had

 

[Wizard]

whats wrong with graph paper - thats all we ever had

Sure, why not go the whole hog and do it on chunks of slate

Bloody luddites

 

[ovenpaa]

Well I am stunned by the standard and quality of replies I have received, why weren't you lot around 30 something years ago when I had homework to do?

Many thanks everyone - I have loads to work with now

Cheers