Do I need a macro?

[lisa-jane]

Hello,

I have a (possibly) complex scenario (which I'm sure to struggle to explain!) where I want to be able to pick from a list of drop down options in cell A1 for example, and then have cell C1, E1, G1, all become set to their required variables based on the choice made from the drop down list in A1 (it is important that the cells which react to the "pick from" list are C1, E1, G1 and not consecutive).

I imagine on another worksheet I would have all the data in rows and columns which feed into the cells in the first worksheet.

No idea how to do this and my knowledge of excel is REALLY small!

Help please!??!

 

[mole999]

sounds pretty easy

whats your list of values

 

[lisa-jane]

<table style="border-collapse: collapse;" width="379" border="0" cellpadding="0" cellspacing="0"> <col style="" width="121"> <col style="" span="6" width="43"> <tbody><tr height="13"> <td height="13" width="121">
</td> <td colspan="3" width="129">Absoption Coefficient</td> <td width="43">
</td> <td width="43">
</td> <td width="43">
</td> </tr> <tr style="" height="26"> <td class="xl26" height="26">Material</td> <td class="xl27">125Hz</td> <td class="xl27">250Hz</td> <td class="xl27">500Hz</td> <td class="xl27">1kHz</td> <td class="xl27">2kHz</td> <td class="xl27">4kHz</td> </tr> <tr height="13"> <td class="xl24" height="13">Carpet</td> <td class="xl25">0.01</td> <td class="xl25">0.02</td> <td class="xl25">0.06</td> <td class="xl25">0.15</td> <td class="xl25">0.25</td> <td class="xl25">0.45</td> </tr> <tr height="13"> <td class="xl24" height="13">Concrete (rough)</td> <td class="xl25">0.01</td> <td class="xl25">0.02</td> <td class="xl25">0.04</td> <td class="xl25">0.06</td> <td class="xl25">0.08</td> <td class="xl25">0.10</td> </tr> <tr height="13"> <td class="xl24" height="13">Concrete (sealed)</td> <td class="xl25">0.01</td> <td class="xl25">0.01</td> <td class="xl25">0.02</td> <td class="xl25">0.02</td> <td class="xl25">0.02</td> <td class="xl25">0.02
</td> </tr> </tbody></table>
Above is the data I imagine in the 2nd worksheet.
On the 1st worksheet, A1 for example might be options chosen from a list like: carpet, concrete (rough), concrete (sealed) etc.
(reading the column from 2nd worksheet).
and then in the 1st page work-sheet, if A1 is "carpet", C1 will automatically be 0.01, E1 will be 0.02 and G1 will be 0.06
(reads across the row of "carpet", but if I change A1 to "concrete (rough)" (from the pick list) all the values in C1, E1, G1 etc.
automatically change to the values for "concrete (rough)":
<table width="379" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="13"><tr height="13"><td class="xl25">C1=0.01</td><td class="xl25">E1=0.02</td><td class="xl25">G1=0.04</td></tr></tr></tbody></table>
Does that make sense?

Thanks!

 

[mole999]

yes that makes sense

on your lookup page (give it a name, you can hide the page to stop it being played with)

set your values, doesn't have to be column A

Material 125Hz 250Hz 500Hz 1kHz 2kHz 4kHz
Carpet 0.01 0.02 0.06 0.15 0.25 0.45
Concrete (rough) 0.01 0.02 0.04 0.06 0.08 0.10
Concrete (sealed) 0.01 0.01 0.02 0.02 0.02 0.02

with all your entries in, select from carpet to 0.02 of concrete sealed (ALL the data)

in 2003 from the title bar Insert > Name > Define and call the range maybe absorption

in A1 on your main sheet you need to complete a list box that takes the values from your reference page, just the main names carpet, concrete etc

in C1 you have =vlookup($A$1,absorbtion,2,0)

in E1 you have =vlookup($A$1,absorbtion,3,0)

in G1 you have =vlookup($A$1,absorbtion,4,0)

might need a tweak here or there

 

[lisa-jane]

Thank you.
I'll give that a try in the morning.
I don't know how to make the list box, so I'll have to sort that out first.
Then I'll try to follow your instructions.

I'll keep you posted!

lisa-jane.