Validation list with formula?

[SaM]

I'm not sure I am going to explain this right - but here goes. I have a
project open in excel - the main page is a form to fill in, with which I use
drop down lists (via data->Validation then choose list.

My data is on a different sheet in the same work book. What I would like to
do is when a user selects one item from a specific drop down list, it would
pull one of many from a specific list.

The section is:
Reason for Change Info:
Category: Reason:
Under category there is a list of 6 categories, numbered 1 - 6. When they
select any one of the 1 - 6 categories, I would like the Reason Drop down
list to pull the specific reasons to that specific category.

The category is filed:
Category
1 Construction
2 Design/Eng.
3 Vendor
4 General Delays
5 Project Scope
6 Turnaround


My table for reasons looks like this:
Category: 1 2 3 4 5 6
Reason: name name name name name name

With Name being the specific reason.

The drop downs work fine - but I want to do a look up and cannot quite
figure out how I can do this....any ideas anyone?

 

[Gary Brown]

Have you tried the HLookup( ) function?

Horizontal lookup vs. Vertical lookup (VLookup)

 

[SaM]

Yes, I did try that. I ended up using an IF statement in the Validation box
for the drop down list that includes a statement as:

=IF($B$60="Construction",Construction_Reason,IF($B$60="Design/Eng.",Design_Eng,IF($B$60="Vendor",Vendor,IF($B$60="General
Delays",General_Delays,IF($B$60="Project
Scope",Project_Scope,IF($B$60="Turnaround",Turnaround,Error))))))

That did the trick.

 

[Ashish Mathur]

Hi,

To create a dependent validation list, refer to the following link -
http://www.contextures.com/xlDataVal02.html

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com