Point to another different sheet in Excel

Question

I am trying to find a way from Cell to get data from a cell in a Sheet that lies to the left (down in the tray) of the current sheet.

I know how to call other sheets through

=Sheet1!A1 

But now I need to explain something best with

 =Sheet[-1]!A1 

Any ideas?

Answer 1

Using tab order as a fundamental part of your calculations is a complex and risky approach to Excel calculations. Excel offers many alternatives that you should use:

• A simplified version of the belisarius clause is: =INDIRECT(A1 & "!A2") , where cell A1 has the name of your data sheet and A2 has the name of your target cell in your data source sheet. If you know the name of your list of interest (or you can look it up somehow), use this method.

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

• If you absolutely want to follow the VBA route, you will have to write code that:
  3a. Captures all active book names and stores them in an array.
  3b. Gets the index number of the currently active workbook in this array. Subtract 1 from this index to get the sheet to the left.
  3c. Gets the cell value from this sheet.

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

Edit

The whole idea with this is that you have the sheets and the ability to move them, and that will change the calculations. - Gnutt 1 hour ago

Please, please do not do this. Firstly, this is not a standard method of interacting with a spreadsheet. Your end users are likely to be confused and may not even ask for clarification.

You need to study the idea of data validation :

• Using Data-> Validation , create a drop-down menu that lists all the sheets in the book (if the names of all sheets are static, you can just hardcode them, you need VBA to pull them out).
• Then the user simply selects the sheet of his choice, and the indirect () will automatically update everything.

Alternatively, you can also check Tools-> Scripts . I do not know anyone who uses this feature, but you can be a good candidate for this. In principle, it allows you to see the results of calculations using different data sets (for example, “scenarios”), so the user can switch between them.

Using one of the two methods above, you can completely avoid VBA, thereby saving users who annoy the warning message when they open your file.

Answer 2

 =INDIRECT("Sheet"&TEXT(VALUE(MID(CELL("filename",A8),FIND("]",CELL("filename",A8))+1,256))-1,"#")&"!A1") 

Cautions:

• Your book must be saved before.
• A8 can be replaced with a link to any cell without errors

Answer 3

I know that this is not considered good practice here, but I want to do something like this. And it replicates the functionality of the database to some extent, but I don’t have the time or support to build it from scratch when there is something already half in place.

The reason I want to do this is to create a pivot table that references all the worksheets in the book, and automatically expands if you insert a new worksheet. This is the management of large large trading / reporting tables with many different business units that have the same structure (that is, they use the same sheet format to report the same results to different people. There is high staff turnover. Sheets reporting on various aspects of the source code, which takes a long time to cope with recreating all the tables each time.

You can use string () as an index marker to determine the information you want to use, such as REPLACE, OFFSET, or INDEX, but you cannot, because they apply only to 2D arrays.

While Excel treats three-dimensional links as arrays for aggregate functions, it does not seem to do the same for referenced functions. You can have SUM (sheetX: sheetY! A1) and be able to add a sheet between them, there is no (for example) INDEX function (sheetX: sheetY! A1, n). I tried to experiment using these 2D functions as part of the array formulas and define a three-dimensional reference as an array or a named range ... well, it was worth it :).

Therefore, I consider this a valid action. I also believe that there should be a way to do this, but so far I am abandoning the UDF, which is prone to errors caused by calculation problems, or is manipulating the Workbook_SheetChange function or similar. Or create a single master list to manage all the others that are filled in using a routine based on an array of all books.

Answer 4

These features work well for me. They get the index of the worksheet (the parent of the range with which you call them), add or subtract from this index, and then create a range from this (relative) sheet and the passed address.

 Function relativeSheet(r As Range, iRelative As Integer) Application.Volatile relativeSheet = Sheets(r.Cells(1, 1).Parent.Index + iRelative).Range(r.Address) End Function Function prevSheet(r As Range) prevSheet = relativeSheet(r, -1) End Function Function nextSheet(r As Range) nextSheet = relativeSheet(r, 1) End Function