Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

David Z asked in Computers & InternetSoftware · 10 years ago

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?

2 Answers

Relevance
  • Anonymous
    10 years ago
    Favorite 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.

  • 10 years ago

    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"

Still have questions? Get your answers by asking now.