Referencing a named range through another cell

[GKlucsarits]

Hi All,

Thanks for the recent help on some other issues. I hope this isn't something obvious that I'm missing, but I can't seem to find a simple way to do what I want.


I have a large number of named ranges in a table. In another cell I want to dynamically recall the sum of the range based on the name of the range in an adjoining cell.


For example: Range1 =$B$1:$B$10, Range2 =$C2$C10, etc.


On another sheet, these various ranges appear in different sequences depending on other criteria, so for example it might be Range9, Range3, Range6, etc.


Is there a way that I can have a value in a cell, like Range2 and have the adjacent cell return the formula =SUM('Range3')? Am I missing an obvious way to reference ranges in formulas?


As always, assistance is much appreciated.


Thanks and regards,


George

 

[Luke M]

Assuming cell A2 has the name "Range2" in it, your formula to find the sum of Range2 is:

=SUM(INDIRECT(A2))


Indirect is used whenever you want to take a text string, and/or value from a cell and use it as a cell/range reference.

 

[GKlucsarits]

Hi Luke,


Thanks, and sorry for not clarifying what I wrote earlier. I should have noted that the format of the Range titles is usually something like, "Power Up Failure"; when Excel creates the name it's in the form of: Power_Up_Failure. Is there any way to reference the range Power_Up_Failure when the cell value says: "Power Up Failure"?


The only option I could think of was using a helper column to convert the text value to a named range format using SUBSTITUTE to replace the spaces with underscores, but this isn't always practical given the large number of changing range names.


Cheers,


George

 

[Luke M]

IF you want to use a rule essentially saying that any names with spaces should have the spaces replaced with underscores, you can modify my earlier formula to:

=SUM(INDIRECT(SUBSTITUTE(A2," ","_")))


Your other otpion is to have a list of all your named ranges (Insert - Name - Paste is handy) and use this list as a data validation for the cell the user inputs goes into.

 

[sfav8r]

GKlucsarits


Thanks for your post. I needed to reference a named range in a cell from a combobox and I couldn't make it work. The "Indirect" is exactly what I needed. You ended 2 hours of frustration for me!

 

[alanj]

Learned a lot from this post - I have a question to build on this.


I used named ranges on a table, using the "create from selected" method, selecting both "column" and "rows". The top row is months (Jan, Feb), and the Columns are names a complicated "G_All_Blah_Blah". I want to create a sumifs formula on another page that can cross reference a single cell (and a range), based on parameters from drop down menus that reference the named columns and rows. I realize I can use the indirect formula, but how can I do this to find a cross reference between columns and rows, using named ranges?