Value from cell in previous sheet

Tom,

there is no concept of "previous" with regard to tables or sheets. The best you can do is to either number the sheets so they had a number at the end or make a table that contains an "index" (so to speak) that maps a number to a name.

You can have a small table where you enter the "index" or number to identify that sheet and therefore its' relationship to other sheets.

All this can be tied together using the indirect() function which will take a string and convert it to a reference. the "string" can be assebled as needed.

Here is one way:

Create three sheets:

- Summary

- Sheet 1

- Sheet 2

for the sheet "Summary":

create a table like this:

For the second sheet titles "Sheet 1":

create a table named "Table 1" as shown:

On this table place a "1" in cell A1 as the sheet identifier and add two values as shown

Duplicate this sheet and change the sheet name (of the new sheet) to "Sheet 2" AND change the value in cell A1 to "2". Now the magic:

In cell B2 add the formula:

B2=INDIRECT(VLOOKUP(A1−1, Tables::A:B, 2, 0)&"::Table 1::B2")

this is shorthand for select cell B2, then type (or copy and paste from here) the formula:

"=INDIRECT(VLOOKUP(A1−1, Tables::A:B, 2, 0)&"::Table 1::B2")" without the first and last double quotes

This now gets the table name for the previous table (as defined by the table on the "Summary" sheet then retrieves the value from cell B2.

You can add another sheet:

0) add "Sheet 3" to the next row in summary sheet

1) duplicate this sheet (Sheet 3) and

2) rename the new sheet as "Sheet 3" then

3) change the value in cell A1 of the table to "3"

Hi tomski84,

As far as I know there isn't a "previous sheet" reference.

But a simple way to get close to what you want, assuming your sheets all have a standardized structure, could look like this:

=INDIRECT("MySheet "&Sheet Suffix::B2&"::Table 1::A1")

in place of:

=MySheet 1::Table 1::A1

If you set up your formulas this way and name your sheets with sequential suffixes, then you can Duplicate the sheet and all you have to do is change the value in column A of a separate 'Sheet Suffix' table. (Column has a formula of =A2−1)

If you're doing a lot of this, you can automate further using AppleScript. For ideas see link at Mac OS X Automation.

SG

If you've adopted Wayne's system or similar you can use the following AppleScript to generate (and subsequently update) the values for the Tables table used for the lookup. Whenever you need to update/refresh that table (say, after you've added new sheets or renamed some) just run the script while the document is open, click once in the upper-left cell of the range where you want the values, and paste. The sheet number will paste into the first column and sheet Name into the second.

--get number and name for each sheet in a document for pasting into a lookup table

try

tell application "Numbers" to tell the front document

set paste_str to ""

repeat with i from 1 to count sheets

set paste_str to paste_str & i & tab & name of sheet i & return

end repeat

set the clipboard topaste_str

end tell

display notification "Ready to paste" with title "Numbers"

on error

tell application "System Events" to display alert "Did you select an open Numbers document?" buttons "Cancel"

end try

--end of script

SG