Linking a range of data from another sheet with its name in a cell
Linking a range of data from another sheet with its name in a cell
(OP)
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.
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.





RE: Linking a range of data from another sheet with its name in a cell
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.