data validation

B

Blades

Hi,

I have a problem with data validation. My validation is dependant on two
seperate cells. I have created two lists, the first is a list of beam types,
the second is a list of beam sizes. Now what i want to be able to do is allow
the user to select a beam type and then select a beam size and have the
correct cross sectional area appear in a seperate cell.

Can anyone give me a formula to allow me to do this?

Thanks in advance!
 
M

Max

Blades said:
I have a problem with data validation. My validation is dependent on two
seperate cells. I have created two lists, the first is a list of beam types,
the second is a list of beam sizes. Now what i want to be able to do is allow
the user to select a beam type and then select a beam size and have the
correct cross sectional area appear in a seperate cell.
Can anyone give me a formula to allow me to do this?
Click to expand...

Assuming the DV for Beam_Size is in A1, DV for Beam_Type in B1

Then something like this in C1, array-entered
i.e. press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(Beam_CrossSectArea,match(1,(Beam_Size=A1)*(Beam_Type=B1),0))

The 3 defined names:

Beam_Size
Beam_Type
Beam_CrossSectArea

are assumed identically sized ranges
 
Top