Obtain data from other sheets

Studying an example comparing the use of INDIRECT in Excel and Numbers may help. Numbers is, of course, the one on top; Excel is the lower one.

In each I have three sheets. The screenshot show the first sheet. The other two sheets in Excel, respectively, have the value of 2 and 3 in cell A1. The other two sheets in Numbers have the value of 2 and 3 in cell A1 of a table in on each of those sheets named Table 1 (the default name that Numbers gives to the first table you create on a sheet).

Noting the following may help:

1. Excel uses ! as the separator whereas Numbers uses ::
2. Excel doesn't use a table name whereas Numbers always needs a table name.

You can see what reference you need to construct in Numbers by typing = and clicking the source cell on the other sheet. Numbers will omit the sheet name in the reference if the table's name is unique in the entire document.

A final note: I try to avoid using INDIRECT whenever I can, in both Excel and Numbers. It's very powerful but I find it "clunky" to use. Plus it's a "volatile" function, meaning it has to recalculate every time you make any change anywhere in your document, which potentially can slow things down when you are working with your document. Often there are simpler ways to achieve the same end.

SG

Hi Dhand,

I think SG is asking more for particulars of what you are wanting to accomplish rather than just addressing your question of "How do I use INDIRECT() ADDRESS() in Numbers. Excel and Numbers work to the same ends but by slightly different means.

Some terminology

An Excel Workbook would be called a Spreadsheet or a Document or a file.

A document can have many sheets (worksheets)

A sheet contains a canvas which can have many tables, textboxes or other objects on it.

If a table has a unique name you do not need to specify its sheet. "Sheet 15::UniqueTableName::A2" and "UniqueTableName::A2" are equivalent.

I think you are used to doing something like this:

As long as "DataTable" is a unique name it can be on any sheet in the document.

quinn

Hi Dhand,

Here is an approximation of your situation. I used different tables on the same sheet to make the visuals clear.

If your cell F2 is simply a sheet name then you need to add :: before your Table 1.

i.e. INDIRECT(F$2&"::Table 1::G4:G200")

If you want to copy and paste (no screenshot) your formula into a reply taht might be the easiest way to get it fixed. There are various errors.

quinn

Dhand4711 wrote:

Hi

this gives syntax errors.

DH

I don't see a syntax error in the formula. You may have an invalid reference error if the value in D70 does not match the name of the sheet exactly (including spaces and capitalization) or the table on that sheet is not called Table 1 (again, with the same capitalization and the spaces) or there are not 200 or more rows in the table. The formula below works as intended in my test spreadsheet (as far as I can tell); no syntax or other errors.

=SUMIF(INDIRECT(F$2&"::Table 1::G4:G200"),$D70,INDIRECT(F$2&"::Table 1::D4:D200"))

Hi Dhand,

"invalid reference" suggests that one of the three references are not directing where you want them to.

Check your value in F2 and D70. Make sure you have 200 rows and column G in the sheet and table specified.

quinn

Just the way you would do this in Excel.

If you are, say, in a table in Sheet 1 and want a value from a table in Sheet 2, do something like this:

1. Click the destination cell and type = to call up the Formula Editor
2. Navigate to Sheet 2
3. Click the source cell that you want.
4. Hit Enter.

SG

Dhand4711 wrote:

is there no equivalent of excel indirect address function.

Yes, there is. But perhaps you could post specifics on what you are trying to do so contributors here can give you ideas on how to do the equivalent in Numbers.... Thanks for the gold star!

SG

Excel has worksheets and each worksheet is a monolithic table. Numbers has sheets and each sheet can have multiple tables on it. When you import from Excel, the worksheet name becomes the name of a sheet in Numbers and the table is given the name Table 1. A cell that in Excel is Sheet2!D1 will become Sheet2::Table 1::D1 in Numbers.

The Excel formula SUMIF(INDIRECT(F$2&"!G4"):INDIRECT(F$2&"!G200),$D70,INDIRECT(F$2&"!D4":F$2&"!D20 0"))

written in Numbers it would be SUMIF(INDIRECT(F$2&"::Table 1::G4"):INDIRECT(F$2&"::Table 1::G200),$D70,INDIRECT(F$2&"::Table 1::D4":F$2&"::Table 1::D200"))

Unless I made a mistake (in which case I apologize).

Or, simpler would be

SUMIF(INDIRECT(F$2&"::Table 1::G4:G200),$D70,INDIRECT(F$2&"::Table 1::D4:D200"))

I cannot find a simple way to turn an unsupported Excel formula into text so that it can be imported into Numbers without being replaced by its most recent value. If we could do that, we could use Find/Replace to fix the formulas. You replace all "!" with "::Table 1::". The best I can come up with is to use the Excel preference "Show formulas" to show the formulas, copy the entire sheet of formulas, apply the script "set clipboard to text", then paste them into a Numbers table that has been pre-formatted with all cells as text.

Dhand4711 wrote:

In my trials I tried to simply access a given cell on a detail sheet, but the best that I managed was the name of the sheet plus table 1 but not the cell reference.

Badunit is, of course right on. Numbers always needs a table name.

The easiest way to see how Numbers constructs a reference is to click in an empty cell, type = to call up the formula editor, and then click the cell (in an another table and perhaps on another sheet) to which you want to refer.

PS. why is there no character by character editor that can be used on formulae, or is there a method that I have not found?

The formula editor that pops up when you click a cell and type = allows you to edit character by character. If you're having trouble getting used to the address tokens (the "lozenges") then you can try copy-pasting the formula into a text box and editing it there.

SG

Hi Dhand,

I am not sure why you consider the lozenges a problem. They are a quick visual hat tells you Numbers understands your reference (or in some cases mis-understands your reference.

I think your input is off:

"I cannot input the Table 1:D4:D200 "

Should be Table 1::D4::D200. Notice the double colon.

quinn