Linking a range of data from another sheet with its name in a cell 1

[gwm]

I have an excel spreadsheet that has a defined range of cells on SHEET2 called RANGE_NAME. On SHEET1 I have a cell that contains the text name of the range, or "RANGE_NAME".

I am trying to figure out how to set up a formula to link RANGE_NAME on SHEET2 to location I have selected on SHEET1 based on the cell contents (there are other ranges on SHEET2 also, and I am selecting which one to bring in by what is in the cell). I have tried using the INDIRECT function, but keep getting syntax error messages. Anyone have ideas on an easy way to do this? I just want to bring in the range of cells I define by its name in the cell contents on SHEET1. Some examples would help me get over the syntax error I am generating. Thanks to you all.

 

[Mala]

Lest's assume the following:
1. Your text 'Range_Name' is in cell B2 of Sheet1
2. The actual range Range_Name refers to is Range D9:G16 on Sheet2 (i.e. 4 columns wide and 8 rows high)

Select a grid of the same size on Sheet1 (say B3:E10)

Type: =INDIRECT($B$2)

Hit Ctrl+Shift+Enter to enter this as an array formula.

The selected range will be populated with the values from Sheet2.

Note: If you select a larger than 4×8 grid, you'll get #N/A error values in the extra cells. If you select a smaller grid, the values lying outside the selected grid size will not appear.