Validation list with formula?
in [Functions]
|
From: SaM on 13 Apr 2010 12:09 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? -- Susan M. Project Controls Data Coordinator
From: Gary Brown on 13 Apr 2010 12:40 Have you tried the HLookup( ) function? Horizontal lookup vs. Vertical lookup (VLookup) -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "SaM" wrote: > 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? > > -- > Susan M. > Project Controls Data Coordinator
From: SaM on 13 Apr 2010 14:30 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. -- Susan M. Project Controls Data Coordinator "Gary Brown" wrote: > Have you tried the HLookup( ) function? > > Horizontal lookup vs. Vertical lookup (VLookup) > -- > Hope this helps. > If it does, please click the Yes button. > Thanks in advance for your feedback. > Gary Brown > > > > "SaM" wrote: > > > 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? > > > > -- > > Susan M. > > Project Controls Data Coordinator
From: Ashish Mathur on 14 Apr 2010 19:12 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 "SaM" <SaM(a)discussions.microsoft.com> wrote in message news:147F2163-3684-4987-B54D-214690A1094B(a)microsoft.com... > 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? > > -- > Susan M. > Project Controls Data Coordinator
|
Pages: 1 Prev: Change the weekends Next: How do I set NETWORKDAYS to regard friday and saturday as week |