Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Reference with INDIRECT, INDEX/MATCH
I have a large spreadsheet with each tab organized as a table, each table has
a dynamic named range. My users want to access data from this main source by selecting a range name from a list (cell with dropdown list). I have been able to get this to work in one workbook by using INDIRECT to capture the range name and INDEX\MATCH to bring in the actual cell contents. When I try to do this with INDIRECT I get the REF# error. Is this something that can be done? Thanks in advance for any help or suggestions. -- Scott S |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Reference with INDIRECT, INDEX/MATCH
INDIRECT requires a *text* representation of a valid reference. Your dynamic
range does not meet this requirement. I don't know what kind of formula you're using a dynamic range in but this is how to get it to work in a simple SUM formula. Assume cell A1 is your drop down list. rng1 is the named dynamic range. If you have only that single named range: =SUM(CHOOSE(MATCH(A1,A1,0),rng1)) If have a few named ranges: =SUM(CHOOSE(MATCH(A1,{"rng1","rng2",rng3"},0),rng1 ,rng2,rng3)) If you have many** named ranges list the names in a range of cells: J1 = rng1 J2 = rng2 J3 = rng3 =SUM(CHOOSE(MATCH(A1,J1:J3,0),rng1,rng2,rng3)) ** limited by the number of value arguments that CHOOSE will accept which is version dependent: Prior to Excel 2007 value arguments = 29 Excel 2007 value arguments = 254 -- Biff Microsoft Excel MVP "ScottS" wrote in message ... I have a large spreadsheet with each tab organized as a table, each table has a dynamic named range. My users want to access data from this main source by selecting a range name from a list (cell with dropdown list). I have been able to get this to work in one workbook by using INDIRECT to capture the range name and INDEX\MATCH to bring in the actual cell contents. When I try to do this with INDIRECT I get the REF# error. Is this something that can be done? Thanks in advance for any help or suggestions. -- Scott S |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Reference with INDIRECT, INDEX/MATCH
"T. Valko" wrote...
.... ** limited by the number of value arguments that CHOOSE will accept which is version dependent: Prior to Excel 2007 value arguments = 29 .... Then why can I enter =SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30) which Excel accepts and returns 465? Maybe you mean 30 arguments? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Reference with INDIRECT, INDEX/MATCH
Thanks for your help with this. I'll check it out.
-- Scott S "T. Valko" wrote: INDIRECT requires a *text* representation of a valid reference. Your dynamic range does not meet this requirement. I don't know what kind of formula you're using a dynamic range in but this is how to get it to work in a simple SUM formula. Assume cell A1 is your drop down list. rng1 is the named dynamic range. If you have only that single named range: =SUM(CHOOSE(MATCH(A1,A1,0),rng1)) If have a few named ranges: =SUM(CHOOSE(MATCH(A1,{"rng1","rng2",rng3"},0),rng1 ,rng2,rng3)) If you have many** named ranges list the names in a range of cells: J1 = rng1 J2 = rng2 J3 = rng3 =SUM(CHOOSE(MATCH(A1,J1:J3,0),rng1,rng2,rng3)) ** limited by the number of value arguments that CHOOSE will accept which is version dependent: Prior to Excel 2007 value arguments = 29 Excel 2007 value arguments = 254 -- Biff Microsoft Excel MVP "ScottS" wrote in message ... I have a large spreadsheet with each tab organized as a table, each table has a dynamic named range. My users want to access data from this main source by selecting a range name from a list (cell with dropdown list). I have been able to get this to work in one workbook by using INDIRECT to capture the range name and INDEX\MATCH to bring in the actual cell contents. When I try to do this with INDIRECT I get the REF# error. Is this something that can be done? Thanks in advance for any help or suggestions. -- Scott S |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Reference with INDIRECT, INDEX/MATCH
"Harlan Grove" wrote in message
... "T. Valko" wrote... ... ** limited by the number of value arguments that CHOOSE will accept which is version dependent: Prior to Excel 2007 value arguments = 29 ... Then why can I enter =SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30) which Excel accepts and returns 465? Maybe you mean 30 arguments? You've misunderstood what I was referring to about "value arguments". CHOOSE(index_num,value1,value2,value3...value29) -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect function in Index/Match Array | Excel Discussion (Misc queries) | |||
Match, Index, Indirect ? | Excel Worksheet Functions | |||
Index,Indirect, and Match | Excel Worksheet Functions | |||
Index match formula works in for one worksheet reference but not another | Excel Worksheet Functions | |||
INDEX - MATCH - VLOOKUP - returning missing reference | Excel Discussion (Misc queries) |