Forum Discussion
Access different worksheets based on contents of a cell
I would like to access data from different worksheets based on the contents of a cell in the current worksheet without re-doing all the formulae. Basically, I have a template worksheet and want to populate it with data from one of a set of identical other worksheets based on the contents of a cell in the template worksheet. Is there a way to use the contents of a cell within another cell formula (I call it indirect referencing) to retrieve data from the referenced worksheet? What I would like to do is automatically populate a template worksheet based on data I enter into a cell; e.g., if I enter "1" in the cell I would get Sheet1 data, "2" would get me Sheet2 data, etc. It should be easy to do but I haven't found a way yet.
Anyone have suggestions?
Are your sheets literally named Sheet1, Sheet2, etc.?
If so: enter a number such as 2 in a cell, say A1.
In another cell, enter the formula
=INDIRECT("Sheet"&$A$1&"!D3")
to return the value of D3 on the indicated sheet, or
=INDIRECT("Sheet"&$A$1&"!D3:F20")
to return a multi-cell range.
2 Replies
Are your sheets literally named Sheet1, Sheet2, etc.?
If so: enter a number such as 2 in a cell, say A1.
In another cell, enter the formula
=INDIRECT("Sheet"&$A$1&"!D3")
to return the value of D3 on the indicated sheet, or
=INDIRECT("Sheet"&$A$1&"!D3:F20")
to return a multi-cell range.
- Thanks Hans, it worked. I played around with the INDIRECT function but missed the part where the cell number is concatenated to the end of the argument string as text.
