Excel 2003

[damo]

1. Does anyone know if I can select a value from List Box 1 (see file at link) and have the correct value appear in list box 2? e.g. if I select water in list box 1 can the density of water (1000 g/L) appear in list box 2?

2. How can I set the properties of the List Box to visible in the worksheet and invisible when printed?

[EdMac]

Suggest you use VLOOKUP.

In the attached I have created a table called Density using the values in sheet 2.

As far as not being able to see the value of some cells when printing, you would probably need to go for a VB solution.

Ed

[damo]

Suggest you use VLOOKUP.

In the attached I have created a table called Density using the values in sheet 2.

As far as not being able to see the value of some cells when printing, you would probably need to go for a VB solution.

Ed

Thanks for that. It is exactly what I am after. Your help is much appreciated. Just one more question if I may. Can I use the VLOOKUP function if I want a user to choose bewteen 2 or more options in List Box 2? e.g. if the user chooses diesel as the liquid, can I allow the user to choose from 2 different densities e.g. 850 g/L or 900 g/L?

[EdMac]

You're welcome.

Thanks for the feedback

Ed

[EdMac]

Damo,

Lookup will only match one item. If you needed to have a choice you would need to redefine diesel e.g. Diesel light, Diesel heavy so that it would then lookup the apppropriate corresponding value.

Ed

[damo]

Damo,

Lookup will only match one item. If you needed to have a choice you would need to redefine diesel e.g. Diesel light, Diesel heavy so that it would then lookup the apppropriate corresponding value.

Ed

Thanks for that. But do you know if there is another way to achieve this, so that I can list diesel and have 2 different densities for it? Can I allow a user to select diesel from List Box 1 and then have them select from 2 different densities for diesel from List Box 2?

Also do you know how to set the List Box properties so that it is visible onscreen but invisible when printed?

[EdMac]

Hi Damo,

Yes, it is possible but you would need to approach it differently by using dependent drop down boxes.

Can you let me have a list of all the requirements and I will see what I can do.

In the meantime, theis site gives a good explanation of dependent lists

http://www.contextures.com/xlDataVal02.html.

The list box is just another cell, the only way I can think of to achieve what you are suggesting would be to have a macro that runs on printing and which would change the text colour to the background colour - beyond me, I'm afraid.

Ed