How to reference a cell from another worksheet

HI Andj',

Not enough information to determine what is wrong or why.

I see two tables, each with a selected cell.

The formula bar for the first shows a reference to cell E128 on Table 1 of the sheet "Bundrant B2/B22" and the selecte cell displays the amount 5.25.

The formula editor for the second shows a reference to a different cell, E126, on the same table, and the selected cell displays the amount 6.25.

I assume that neither of the screen shots shows (part of) Table 1 on the sheet "Bundrant B2/B22."

What do cells E126 and E128 on that table contain?

Regarding the date in row 125 of the second table: This is a format change—the setting for Time (of day) has been changed from 'none' to the format shown.

All dates or times in Numbers are actually Date and Time values representing an instant in time. Both parts (Date and Time) are always present, but not always displayed. Select the cell, click the Format brust, choose Cell and format as Date and Time with time set to none an date set to the format you want.

With the information supplied, I don't know what caused the format to change on this cell.

Rgards,

Barry

When you enter the date, are you entering a complete, valid date?

"11/10" may be a valid (Oct of 2010) but is probably not what you meant

enter 11/1/2010

also, select the data column and select your desired format from the cell formatter

please email the document to me. My email is in the bio of my profile

When I opened this document I can see that the date column has cells with different format selections. This is evident from the "—" before "Automatic" and "Date & Time" in the "Date Format" menu of the cell formatter:

to resolve this, select any cell in the table, then select the column (by clicking the "A" at the top of the column), now open the cell formatter and select "Date & Time":

Now use the "Date" and "Time" menus of the "Date & Time" formatter. In the "Date" formatter you can see you current document has 4 different selections. Choose the single on you want WHILE THE date column is selected. Now set the "Time" to "None" since you do not want to show the time.

Repeat as needed for each persons sheet

Andj606 wrote:

WHAT is going on with the formats and formulas????

Assuming Wayne has helped you fix the date formats, do you still have strange problems with the formulas? If so, a relevant question would be to ask whether this is a "legacy" document in the sense that you first created it in Numbers 2 (or Excel) and then later brought it into Numbers 3. If that is the case, then your best bet might be to create a new document in Numbers 3 and reconstruct the formulas from scratch. Some documents just don't seem to import well into Numbers 3.

SG

Andj606 wrote:

Yes - This document was created three or more years ago in an earlier version of numbers Recreating this entire document would be a nightmare. I could recreate the balances table that contains the problematic formulas but then do I have to constantly verify that my formulas are working?- shouldn't this program perform the basic functions of a spreadsheet?

If the document was created in an earlier version of Numbers and it is exhibiting this behavior after being imported into Numbers 3, I would HIGHLY recommend starting a new document in Numbers 3. You can copy-paste data into the new document, then rebuild the formulas there.

If you have adopted good spreadsheet design then you will have only a handful of formulas to reconstruct anyway. If you find you do have lots of different formulas then this might be an ideal opportunity to redesign and simplify so you have the data in an efficient form and just a few formulas that operate on that data.

Having a look at the templates that come with Numbers 3 (at File > New in your menu) can be very helpful in seeing what is good design in Numbers. In general is not best practices in Numbers to have a formula in the middle of tables at E127 or D72 surrounded by a bunch of blank rows (as shown in the screenshots). Numbers (and indeed recent versions of Excel) works much better with compact tables containing data in the body rows and formulas in Footer Rows or in separate summary tables.

SG

I have been looking at the formulas and while there is an easy selection of formulas I am not 100% certain the modifications are OK.

Summary of the document:

1) a sheet to summarize the balance from each of may sheets

2) many sheets which each represent a tenant (or unit in a rental facility)

- A single table which contains

A) tenant information

B) payment information

Here is a list of proposed changes:

0) Name each sheet by the unit only e.g. "A1" or "T1" or "B2", etc

1) in the sheet for each unit (or tenant) split the single table into

A) tenant information (and name the table "Tenant Information")

B) balance information for this tenant (and name this table "Balance")

2) select both tables on each sheet and and make the table title visible (in the Table formatter)

3) in the "Balance" table remove the formula that calculates the current balance

4) make the first row of each table a header row

Like this I made up some new tabs :

Sheet T1:

Sheet T2:

Sheet T3:

Now the summary sheet:

Add a new column (if you want) to show the tenants name and update the formulas as follows (assuming you add the tenant name column):

D3=IFERROR(IF(COUNTA(B3:C3)<2, "",VLOOKUP("Tenant:", INDIRECT(B3&C3&"::Tenant Information::A:B",TRUE), 2, 0) ), "SHEET NOT FOUND")

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

=IFERROR(IF(COUNTA(B3:C3)<2, "",VLOOKUP("Tenant:", INDIRECT(B3&C3&"::Tenant Information::A:B",TRUE), 2, 0) ), "SHEET NOT FOUND")

select cell D3, copy

select cells D3 thru the end of column D (omit the summary rows at the bottom), paste

F3=IFERROR(SUM(INDIRECT(B3&C3&"::Balance::E",TRUE))−SUM(INDIRECT(B3&C3&"::Balanc e::D",TRUE)), "")

select cell F3, copy

select cells F3 thru the end of column F (omit the summary rows at the bottom), paste

The sheet names MUST match the entries in columns B and C AND

you will have to split the multiple entries of multiple units in a single cell into multiple rows:

like this:

I will return the document you emailed me with these modifications via email. Normally I would share via dropbox but I did not sanitize the whole document such that it can be shared this way.

I think my reply covers some of what SG was addressing... namely the spreadsheet formula design was not suitable for your application. You did very well, but the method was not extensible (that is conveniently extended to many).

I proposed a solution that is more extensible.W.R.T.:

"Yes - This document was created three or more years ago in an earlier version of numbers Recreating this entire document would be a nightmare. I could recreate the balances table that contains the problematic formulas but then do I have to constantly verify that my formulas are working?- shouldn't this program perform the basic functions of a spreadsheet? "

The spreadsheet is doing its' job but was not properly programmed. Like any programming we often want the program to do what we want and not what we tell it to do. Understanding the effects of moving and adding rows is essential in addition to understanding how how you want to work.

Please post back if you have questions with your document.