list box and reference values

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I'm trying to create a work sheet that calculates a final value based on the
properties of a specific substance.

I'm using a drop down list to select the specific substance. when that
substance is selected I'd like it's property values entered into their
respective cells.

For example if a substance in cell A1 is selected, four separate values for
that substance should be entered into cells B1, C1, D1, E1.

Cell F1 would have the answer from the calculation done on these properties.

I'm suspecting I'm going to have to deal with arrays, but I'm not swift
enough to figure it out by myself.

Any assistance is greatly appreciated.

WinXP Prosp2, Office XPsp3
 
A simple set of Lookup formulas in B1:E1 would work for you.
That is, if you already have a data list created, with the properties of the
"substances" listed in an accessible format (records in rows - fields
[properties] in columns).

Check out Debra's site for instructions:

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

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Rich,

Presumably, the dropdown should repopulate the same row each time it's used,
not go down and populate the next row, and the next. You can do this with a
list box or combo box or Data validation dropdown. You'll need a list of
the Substances and their properties. Here's an example setup. I've used
row 2 to allow for labels in row 1. If your newsreader isn't using a
non-proportional font, this'll be a mess:

2 B2): =VLOOKUP($A$2, $A$6:$E$7, 2, FALSE)
2 (C2): =VLOOKUP($A$2, $A$6:$E$7, 3, FALSE)
2 (D2): =VLOOKUP($A$2, $A$6:$E$7, 4,FALSE)
2 etc.
A B C D
5 Substance Property1 Property 2 etc.
6 Upsidasium light nice
7 Governmentium moronic corrupt
etc.


The VLOOKUP formulas go in B2, C2, etc. Set up A2 for Data - Validation -
List, and point it at A5:A7 for the list source. The absolute ($)
references are just to make it easy to copy the first formula, B2, across
with the fill handle or copy/paste. F2 will have the your formula for the
result. It will refer to B2 - E2.
 
Back
Top