formula between sheets

Add a second sheet and make (say) cell B2 = J18 on the first sheet. This will require something other than Numbers though. Excel, Open Office, Libre Office, Neo Office can do it, the last 3 are free.

When you say "sheet" what do you mean? Numbers has documents, and within documents sheets (tabs), and within each sheet it has tables and charts and other objects. Numbers of course can easily reference a table on another sheet. But I have a little trouble understanding what you are trying to do. Can you post a screenshot of what you would like your tables to look like?

Also, have you had a look at the Employee Schedule template (File > New and choose from the Template Chooser)? The templates are good starting point.

SG

Hi marknovom,

I do think there is some confusion between "sheet" and "table". In Numbers a sheet is a canvas that can have multiple tables, text boxes and so on. Having 52 or so sheets in a document would be a little awkward to navigate. Having 52 tables on a sheet, a snap.

So lets say you have a sheet with your weekly tables. The tables are named "8/30/15" and so forth.

This formula looks in column A for a table name and builds a cell reference using that. You would be looking for J18 not E2.

As long as there is only one table named "8/30/15" in the document, we can build an address without worrying about sheet names. These two tables are on the same sheet for convenience. It could be anywhere in your doc.

Does this work for you?

quinn

Hi marknovom,

It does matter that your question involves Google Sheets. In Numbers you can reference a cell llike this: Sheet::Table::Cell. How do you do it in Google Sheets? What would a direct cell reference look like? I suspect you could use INDIRECT() to build your cell reference similarly to how I did it above.

You might have luck asking on a google forum. Csound1 is our resident "anything but Numbers" troll but I have never heard him suggest Google Sheets. Maybe they are similar enough to something he likes that he could provide a a solution.

quinn

Google Sheets uses the same system as everything else, specifically it uses Excels system, (=sheet>cell) in practice no formula writing is need, select the cell that will contain the result and type an = sign, then select the cell that contains the wanted data and press the enter key.

Or you could just name the cell as Bank the reference =#Bank will then collect data from the Bank cell, wherever it happens to be.

"Anything but Numbers" applies when Numbers can not do what the OP wants, but on the occasions when Numbers can it's fine, but I am happy to see that you still have a need to insult.

Hi Csound1,

You haven't actually answered the poster's question. He needs a formula that will return a value from a sheet referenced in the row. This is simply done in Numbers. "=J18" is not going to reference the various tables he needs to reference. Step up and provide a solution.

"Anything but Numbers" is your constant. Reference your initial response in this thread when a simple INDEX() formula is the Numbers solution. "This will require something other than Numbers though." This is simply untrue. I am calling it how I see it. You are both unreliable and uninformed when it comes to what is possible with Numbers. You must have a reason for posting other than helping the posters on the Numbers forum. I have been unable to come up with a flattering motivation for your posts.

quinn

There has been no trolling here, I think. There is legitimate confusion about what posters mean when they use the word "sheet." Generally I take "sheet" to mean a "tab" in Excel or Google Sheets or Numbers.

There is the added potential for confusion: in Numbers a cell must be in a table on a sheet. In Excel a cell can be directly in a sheet, or in a table in a sheet. (Google Sheets doesn't appear to have tables, so I assume cells are always in a sheet.)

All three (and other spreadsheet programs) can easily reference between sheets (tabs) by just using Csound1's method of typing = and then navigating to and clicking in the source cell. The Numbers table-oriented design introduces a lot of convenience (in essence each column and each row in a table with Header Rows and Header Columns is automatically a named range).

Excel and Google Sheets (and others) can also reference another document (also called a workbook in Excel). Numbers cannot do this. All references must be to cells within the same document (either in tables on the same sheet or in tables on another sheet). This limitation can be inconvenient in some use cases but has the virtue of encouraging organization of one's work so that it is not vulnerable to references being "broken" when a folder structure is changed or files are migrated to a new machine, etc.

SG

marknovom wrote:

(Confession: because this is for work, I need to use Google Sheets and not Numbers).

Since you are using Google Sheets, have you had a look at the many templates available for Google Sheets, for example here?

SG

Hi SG,

If I am understanding you, then the solution I proposed above would work on google sheets provided they have INDIRECT(). I always assume that Excel has every function that Numbers has. Maybe google has it covered too.

quinn

t quinn wrote:

Hi Csound1,

You haven't actually answered the poster's question.

"Formula between sheets", although not actually a question that was the OP's subject. The answer (as it often is) Numbers can't do that, my recommendation (also as it often is) don't use Numbers. Feel free to offer workarounds that may or may not match the functionality the OP requested.

t quinn wrote:

Hi SG,

If I am understanding you, then the solution I proposed above would work on google sheets provided they have INDIRECT(). I always assume that Excel has every function that Numbers has. Maybe google has it covered too.

quinn

Hi quinn,

According to this, Google Sheets does indeed have INDIRECT. The example is for reference between different sheets in the same "worksheet" (document).

Csound's latest post, of course, is not based not on fact recognizable to anyone who actually uses Numbers. Just like Excel and Google Sheets, Numbers has convenient reference between sheets. Because of its table-centric design it is more convenient than Excel if one wants to use "named" references. And based on my casual use of Google Sheets, I'd much prefer setting up "inter-sheet" references in Numbers.

I do have some difficulty following exactly what marknovom wants to do, and recommend studying the many templates available for Google Sheets, since that is what he needs to use for work.

SG

Csound's latest post, of course, is not based not on fact recognizable to anyone who actually uses Numbers. Just like Excel and Google Sheets, Numbers has convenient reference between sheets

Then please correct me. How does Numbers handle references between different sheets, how do you add extra sheets to a Numbers file? and then how do you reference a cell in one to receive data from the other?

By sheet I mean a new page containing a table in the same (or another) file. And if in Numbers it has to be on the same sheet (or 'tab' etc) how can that single page/sheet/tab be printed with printing any other data? Can a print area be selected?

Here is a document with two sheets (titled "Sheet 1 " and "Sheet 2")

Each sheet contains two tables titled "Table 1" and "Table 2"

to reference a cell in a table you would use the following form:

=Sheet 1::Table 1::A1

this references cell A1 of table "Table 1" on "Sheet 1"

A range would be:

=Sheet 1::Table 1::A1:B6

the general, canonical form, is

<SHEET>::<TABLE>::<CELL>[:<CELL>]

where the "[ ]" denotes options for ranges

I hope this helps.

I see, thanks for that explanation. (I assume the tabs select which sheet is the active sheet, yes?)