List for three cells (2003 )

Topic

New Reply

Hello Everyone,

I need some help. I have a spreadsheet that I attached where I want the user to select a list based on the data in sheet2. I am able to do a list for the first two (Building/Facility) but am having trouble on how to proceed to the last one. Any ideas would be great.

Reply | Quote

Replies

How about

=INDEX(Data!$C$7:$F$20, MATCH($A$6&$B$6,Data!$C$7:$C$20,0), MATCH($C$6,Data!$C$7:$F$7,0))

See attached, your workbook with the formula & spelling error fixed! –Sam

Reply | Quote

Thanks. I think that would work.

Reply | Quote

Sam,

What about if I want to make the last column the dollar amount instead of Low, Medium, High. Any ideas. I want this to be a list to choose from based on two previous list. Thanks.

Reply | Quote

Insert- name define
Select “Amount”
Change the “Refersto” to:

=OFFSET(INDIRECT($A$6),MATCH($B$6,INDIRECT($A$6),0)-1,2,1,3)

Then you can eliminate the “Value” column and the formula. It will not be needed.

Note: there is a problem with this scheme. You must select Facility, Building, and amount in order. If you change Facility and do not reselect the other items your values will be in error since they are not “reset” by this scheme.

Steve

Reply | Quote