Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with multiple table selection
Hi,
I'm trying to have a dropdown list look at another dropdown list & reference a particular named range based on the 1st dropdown's current selection. The actual amount of selections in dropdown 1 is 3, with 3 different named ranges to reference. I thought I could get away with the multiple options by using a series of CONCATENATE/IF statements, but it seems like I'm doing something wrong. If possible, I'd like to use this style, as I believe it will be useful for future dropdown lists I'll want to add later in my workbook. Here is the formula Iam trying to enter into Data Validation/Source: =CONCATENATE(IF(F4="Analog Path",MAIN_UDP_Selection_Path_Analog,""),IF(F4="Di gital Path",MAIN_UDP_Selection_Path_Digital,""),IF(F4="P assthrough Path",MAIN_UDP_Selection_Path_Passthrough,"")) It tells me I must reference a delimited list, or a single column or row when I try to put this formula in. All 3 named ranges are in the same column, but 2 of these ranges overlap slightly. I can list any of these named ranges as the source (i.e. =MAIN_UDP_Selection_Path_Analog) with no problems, but my formula doesnt seem to output a named range, or maybe its something else I'm missing. Any help would be appreciated. Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with multiple table selection
Well...I was able to solve my problem by renaming all my entries in dropdown
1's named range to single word entries with corresponding named ranges & using a simple INDIRECT function. But I would be interested if anybody has a solution or feedback to the original formula & whether it is a feasible approach...given that I would prefer to have more detailed/wordy named ranges in order to more easily recognize their functions & roles as my workbook grows larger with functions. Thanks. "SteveMax" wrote: Hi, I'm trying to have a dropdown list look at another dropdown list & reference a particular named range based on the 1st dropdown's current selection. The actual amount of selections in dropdown 1 is 3, with 3 different named ranges to reference. I thought I could get away with the multiple options by using a series of CONCATENATE/IF statements, but it seems like I'm doing something wrong. If possible, I'd like to use this style, as I believe it will be useful for future dropdown lists I'll want to add later in my workbook. Here is the formula Iam trying to enter into Data Validation/Source: =CONCATENATE(IF(F4="Analog Path",MAIN_UDP_Selection_Path_Analog,""),IF(F4="Di gital Path",MAIN_UDP_Selection_Path_Digital,""),IF(F4="P assthrough Path",MAIN_UDP_Selection_Path_Passthrough,"")) It tells me I must reference a delimited list, or a single column or row when I try to put this formula in. All 3 named ranges are in the same column, but 2 of these ranges overlap slightly. I can list any of these named ranges as the source (i.e. =MAIN_UDP_Selection_Path_Analog) with no problems, but my formula doesnt seem to output a named range, or maybe its something else I'm missing. Any help would be appreciated. Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with multiple table selection
Hi,
You can also refer to this link for aditional information. Follow this URL " Excel -- Data Validation -- Create Dependent Lists" http://www.contextures.com/xlDataVal02.html Challa Prabhu "SteveMax" wrote: Well...I was able to solve my problem by renaming all my entries in dropdown 1's named range to single word entries with corresponding named ranges & using a simple INDIRECT function. But I would be interested if anybody has a solution or feedback to the original formula & whether it is a feasible approach...given that I would prefer to have more detailed/wordy named ranges in order to more easily recognize their functions & roles as my workbook grows larger with functions. Thanks. "SteveMax" wrote: Hi, I'm trying to have a dropdown list look at another dropdown list & reference a particular named range based on the 1st dropdown's current selection. The actual amount of selections in dropdown 1 is 3, with 3 different named ranges to reference. I thought I could get away with the multiple options by using a series of CONCATENATE/IF statements, but it seems like I'm doing something wrong. If possible, I'd like to use this style, as I believe it will be useful for future dropdown lists I'll want to add later in my workbook. Here is the formula Iam trying to enter into Data Validation/Source: =CONCATENATE(IF(F4="Analog Path",MAIN_UDP_Selection_Path_Analog,""),IF(F4="Di gital Path",MAIN_UDP_Selection_Path_Digital,""),IF(F4="P assthrough Path",MAIN_UDP_Selection_Path_Passthrough,"")) It tells me I must reference a delimited list, or a single column or row when I try to put this formula in. All 3 named ranges are in the same column, but 2 of these ranges overlap slightly. I can list any of these named ranges as the source (i.e. =MAIN_UDP_Selection_Path_Analog) with no problems, but my formula doesnt seem to output a named range, or maybe its something else I'm missing. Any help would be appreciated. Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with multiple table selection
Thanks for the response. I have read that a few times but I believe the main
issue I'm having is related to the CONCATENATE function within data validation. It seems the CONCATENATE function's output, no matter what I seem to do, is not treated as a named range, list, table, etc. It seems to treat it as text only. I would appreciate if anybody could confirm or invalidate my conclusion on that. "challa prabhu" wrote: Hi, You can also refer to this link for aditional information. Follow this URL " Excel -- Data Validation -- Create Dependent Lists" http://www.contextures.com/xlDataVal02.html Challa Prabhu "SteveMax" wrote: Well...I was able to solve my problem by renaming all my entries in dropdown 1's named range to single word entries with corresponding named ranges & using a simple INDIRECT function. But I would be interested if anybody has a solution or feedback to the original formula & whether it is a feasible approach...given that I would prefer to have more detailed/wordy named ranges in order to more easily recognize their functions & roles as my workbook grows larger with functions. Thanks. "SteveMax" wrote: Hi, I'm trying to have a dropdown list look at another dropdown list & reference a particular named range based on the 1st dropdown's current selection. The actual amount of selections in dropdown 1 is 3, with 3 different named ranges to reference. I thought I could get away with the multiple options by using a series of CONCATENATE/IF statements, but it seems like I'm doing something wrong. If possible, I'd like to use this style, as I believe it will be useful for future dropdown lists I'll want to add later in my workbook. Here is the formula Iam trying to enter into Data Validation/Source: =CONCATENATE(IF(F4="Analog Path",MAIN_UDP_Selection_Path_Analog,""),IF(F4="Di gital Path",MAIN_UDP_Selection_Path_Digital,""),IF(F4="P assthrough Path",MAIN_UDP_Selection_Path_Passthrough,"")) It tells me I must reference a delimited list, or a single column or row when I try to put this formula in. All 3 named ranges are in the same column, but 2 of these ranges overlap slightly. I can list any of these named ranges as the source (i.e. =MAIN_UDP_Selection_Path_Analog) with no problems, but my formula doesnt seem to output a named range, or maybe its something else I'm missing. Any help would be appreciated. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
[pivottables] multiple table selection fields update in one click... | Excel Discussion (Misc queries) | |||
Selection of multiple values for pivot table field | Excel Discussion (Misc queries) | |||
multiple pivots updated with selection in one table | Excel Worksheet Functions | |||
Multiple selection in the page area of a pivot table | Excel Discussion (Misc queries) | |||
Data Validation list selection question | Excel Worksheet Functions |