Excel tab reference question?
Is there a way to make a reference to a worksheet tab dynamic? In other words if I have tabs named summary, Jan, Feb and Mar
On summary tab I want to reference cells in Jan, Feb or Mar. but I want reference to look to a column heading in summary tab to determine what other tab to go find data. By changing column heading I want to look to a different tab for result.
Can anyone help?
More Questions From This User See All
Answers & Comments
Verified answer
Yes, but it's a little complicated. (There might be an easier way, but if so, I'm not sure what it is.) First you need to build the whole cell reference (probably using the CONCATENATE function), then around that, wrap the INDIRECT function to get the data from that cell reference.
For example, let's say that on the Summary sheet, you have the name of the sheet you want to pull data from in cell A1 (let's say cell A1 has "Jan" in it for now) and in cells A2 through A6, you want to work on the data from cells A1 through A5 in one of the other sheets. In cell A2, you need to first use the CONCATENATE function to build a reference to data you want, so it would look like: CONCTENATE($A$1, "!A", ROW(A2)-1). [The "ROW(A2)-1)" is so that when you fill the formulas for A3 through A6, it generates the proper row portion of the reference. So, the result in A2 would be "Jan!A1" and if you filled the formula down through A6, A6 would have "Jan!A5".] Now wrap the INDIRECT function around that so you have: =INDIRECT(CONCTENATE($A$1, "!A", ROW(A2)-1))
That's the basic way to do it, but I'm sure your situation will be more complex and you'll be wrapping data analysis functions around that basic portion.
Try the INDIRECT function
For example, if you want the formula to return the value in cell A10 on the "May" tab, try any of the following formulas
=INDIRECT(ADDRESS (10,1,,,B1))
=INDIRECT(B1&"!A10")
Where
B1 - "May"