Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
Good Morning Everyone!
I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
What you need to do is create named ranges for each of the hardware types.
For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
Thanks for the info. I already entered the Name Ranges for each of the 39
types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
Not sure what you mean by "carry over" ?
Did you set B40 to use data validation? -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Thanks for the info. I already entered the Name Ranges for each of the 39 types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
I apologize if I am unclear..... (it must be frustration setting in (LOL)).
Let me restate the situation. Column A extends to 4000 lines, max. The permissible values in Column A (dropdown is called CATEGORY) consist of 1-39 values in the CATEGORY dropdown list. Column B (dropdown is called MFR) represents the Manufacturer of the Hardware listed in Column A. Each of the 39 CATEGORY entries has its own unique list of MANUFACTURERS. (The Category lists contains entries such as VPN, Hub, Modem, PatchPanel, UPS, Server, etc.) For example if users selected the dropdown value of MODEM in A2, A44, A525, A600, A1100, A1555, and A2399 the associated dropdown appearing in Column B (at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without having to enter INDIRECT statements singly. With possibly 15 different people entering data, these "automatic-directed-dropdown" would be invaluable. LOL, I hope this is clearer! Thx, Jeff "T. Valko" wrote: Not sure what you mean by "carry over" ? Did you set B40 to use data validation? -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Thanks for the info. I already entered the Name Ranges for each of the 39 types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
Sorry, I'm not following you on this.
It sounds like you want the dependent drop down to appear in column B without having to "define" a source. That's not possible. -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... I apologize if I am unclear..... (it must be frustration setting in (LOL)). Let me restate the situation. Column A extends to 4000 lines, max. The permissible values in Column A (dropdown is called CATEGORY) consist of 1-39 values in the CATEGORY dropdown list. Column B (dropdown is called MFR) represents the Manufacturer of the Hardware listed in Column A. Each of the 39 CATEGORY entries has its own unique list of MANUFACTURERS. (The Category lists contains entries such as VPN, Hub, Modem, PatchPanel, UPS, Server, etc.) For example if users selected the dropdown value of MODEM in A2, A44, A525, A600, A1100, A1555, and A2399 the associated dropdown appearing in Column B (at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without having to enter INDIRECT statements singly. With possibly 15 different people entering data, these "automatic-directed-dropdown" would be invaluable. LOL, I hope this is clearer! Thx, Jeff "T. Valko" wrote: Not sure what you mean by "carry over" ? Did you set B40 to use data validation? -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Thanks for the info. I already entered the Name Ranges for each of the 39 types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
I will again apologize... having reread my latest reply. I'm on my way out
for the day (a VERY LONG DAY....) and was wondering if it would be permissible to contact you directly at comcast or by phone to present my situation. Thanks, Jeff "T. Valko" wrote: Sorry, I'm not following you on this. It sounds like you want the dependent drop down to appear in column B without having to "define" a source. That's not possible. -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... I apologize if I am unclear..... (it must be frustration setting in (LOL)). Let me restate the situation. Column A extends to 4000 lines, max. The permissible values in Column A (dropdown is called CATEGORY) consist of 1-39 values in the CATEGORY dropdown list. Column B (dropdown is called MFR) represents the Manufacturer of the Hardware listed in Column A. Each of the 39 CATEGORY entries has its own unique list of MANUFACTURERS. (The Category lists contains entries such as VPN, Hub, Modem, PatchPanel, UPS, Server, etc.) For example if users selected the dropdown value of MODEM in A2, A44, A525, A600, A1100, A1555, and A2399 the associated dropdown appearing in Column B (at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without having to enter INDIRECT statements singly. With possibly 15 different people entering data, these "automatic-directed-dropdown" would be invaluable. LOL, I hope this is clearer! Thx, Jeff "T. Valko" wrote: Not sure what you mean by "carry over" ? Did you set B40 to use data validation? -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Thanks for the info. I already entered the Name Ranges for each of the 39 types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependent Dropdown List - Large Scale
I'm at:
xl can help at comcast period net Remove can and change the obvious. -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... I will again apologize... having reread my latest reply. I'm on my way out for the day (a VERY LONG DAY....) and was wondering if it would be permissible to contact you directly at comcast or by phone to present my situation. Thanks, Jeff "T. Valko" wrote: Sorry, I'm not following you on this. It sounds like you want the dependent drop down to appear in column B without having to "define" a source. That's not possible. -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... I apologize if I am unclear..... (it must be frustration setting in (LOL)). Let me restate the situation. Column A extends to 4000 lines, max. The permissible values in Column A (dropdown is called CATEGORY) consist of 1-39 values in the CATEGORY dropdown list. Column B (dropdown is called MFR) represents the Manufacturer of the Hardware listed in Column A. Each of the 39 CATEGORY entries has its own unique list of MANUFACTURERS. (The Category lists contains entries such as VPN, Hub, Modem, PatchPanel, UPS, Server, etc.) For example if users selected the dropdown value of MODEM in A2, A44, A525, A600, A1100, A1555, and A2399 the associated dropdown appearing in Column B (at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without having to enter INDIRECT statements singly. With possibly 15 different people entering data, these "automatic-directed-dropdown" would be invaluable. LOL, I hope this is clearer! Thx, Jeff "T. Valko" wrote: Not sure what you mean by "carry over" ? Did you set B40 to use data validation? -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Thanks for the info. I already entered the Name Ranges for each of the 39 types. A couple of days ago, I used "Indirect" to test each of the 39 Hardware Types and it worked fine. However, I then went to line A40 and entered one of the previous values again, only to find that the dropdown for B40 did not 'carry over' the "Indirect" value from above for the Hardware Type listed at A40. Any thoughts? Thx, Jeff "T. Valko" wrote: What you need to do is create named ranges for each of the hardware types. For example: Named range: Modem Refers to: AE2:AE20 Then, as the source for the drop down in B2 use this formula: =INDIRECT(A2) -- Biff Microsoft Excel MVP "Henn9660" wrote in message ... Good Morning Everyone! I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they do not seem to address what I am specifically trying to accomplish. I have a 4000-line spreadsheet used to track hardware and peripherals. Column A (Category) invokes Data Validation to a list of 39 Hardware Types. Column B2, for example, would contain the name of the Manufacturer for the Hardware Type selected in A2 and so on through A4000. The individual Manufacturer Names [for each of the 39 Hardware Types] in Category are listed in Columns AA through BM. Three of the Categories I use are Modem, Projector, and Hub. What I'm looking for (hopefully) would be a way to use a "large" IF statement that challenges the dropdown values chosen in A2-A4000 to the appropriate Manufacturer Names to be dropdown-displayed in B2...B4000. The 39 Hardware Types are contained [out of sight] in Columns AA-BM. What I would like to do is something like: IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers) OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers) OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers) OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch) Any thoughts would be most greatly appreciated! Thanks, Jeff in Mississippi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent Dropdown List - Not Easy! | Excel Worksheet Functions | |||
Dependent dropdown lists | Excel Discussion (Misc queries) | |||
dependent dropdown boxes | Excel Worksheet Functions | |||
HELP! Nesting IF Statements/Dependent Dropdown List | Excel Worksheet Functions | |||
dependent dropdown | Excel Discussion (Misc queries) |