Search code examples
excelworksheet-function

Point to a relative other Sheet in Excel

I'm trying to find a way to from a Cell get the data from a cell in the Sheet that lies to the Left (down in the tray) of the current Sheet.

I know how to call to other sheets via

=Sheet1!A1

But now I need something best explained with

=Sheet[-1]!A1

Any ideas?

Solution

  • Using the tab order as a fundamental part of your calculations is a complicated and risky approach to Excel calculations. Excel offers many alternatives which you'd be better off using:

    1. A simplified version of belisarius's suggestion is: =INDIRECT(A1 & "!A2") where cell A1 has the name of your datasource sheet and A2 has the name of your target cell in your datasource sheet. If you know the name of your sheet of interest (or can look it up in some way), use this method.

    2. If you need to do this often, you might want to export the data into an actual database (i.e. MS Access). Then you can make standard SQL queries and import the results into your Excel file.

    3. If you absolutely want to go the VBA route, then you'd have to write some code that:
      3a. Grabs all the names of the active workbook and stores them in an array.
      3b. Identifies the index number of the currently active workbook in that array. Subtract 1 from that index to get the sheet to the left.
      3c. Gets the cell value from that sheet.

    4. You can also get freaky with Named Ranges. In Excel 2003, go to Insert->Name->Define, add a new Named Range and you can use that name in your calculations instead of referring to the cell by row and column.

    Edit

    The whole Idea with this one, is that you have the Sheets arranged, and are able to move them around, and that shall change the calculations. – Gnutt 1 hour ago

    Please, please, don't do that. For starters, this isn't a standard method of interaction with a spreadsheet. Your end-users will likely be confused and may not even ask for clarification.

    You'll want to explore the idea of data validation:

    1. Using Data->Validation, make a drop-down menu listing all the sheets in the workbook (if the names of all the sheets are static, you can just hardcode them, otherwise, you'll need some VBA to pull them).
    2. Then the user just picks the sheet of their choice and indirect() will automatically update everything.

    Alternatively, you can also check out Tools->Scenarios. I don't know anybody who uses this feature, but you might be a good candidate for it. Basically, it lets you see the results of calculations using different datasets (i.e. "scenarios") so the user can go back and forth between them.

    Using either of the 2 methods above, there's a good chance you can avoid VBA entirely, thus saving users that annoying warning message when they open your file.