"Cost" isn't a valid reference ...

Question

Level 2

270 points

"Cost" isn't a valid reference ...

I'm new to Numbers - lifelong Excel user/developer.

I'm trying to do something which I beleive should be extremely simple. I have two spreadsheets, one called "Expenses" and one called "Summary." Revenues has three tables.

In the "Summary" spreadsheet, I'm in a table and I'm trying to multiply a cell from the "Fixed Costs" table in "Expenses" by 12 to get the annual fixed costs.

When I type "=" and then use my mouse to select any cell in the "Fixed Costs" table in the "Expenses" spreadsheet, it fails to return a valid reference.

Instead of a valid reference, it just the text "Y1 Cost" ... which generates the error in the subject of this discussion. (See graphic).

All of the other tables in "Expenses" return proper cell references (see below).

I've verified that the cell in question is numeric (its a formula itself).

I've deleted the table and re-created it.

If I try to create a sum (=sum( ... .and then select B2:B3)... it works. But selecting a single cell does not.

Its making me a bit crazy.

I can work around it ... but I'd sure appreciate understanding why its behaving this way, so I can avoid it in the future.

MacBook Pro, iPhone 4GB, Mac OS X (10.4.10)

Posted on Jul 2, 2012 7:44 PM

Reply

Answer 1

Barry

Level 9

59,861 points

Jul 3, 2012 2:44 AM in response to BGist

"I have two spreadsheets, one called "Expenses" and one called "Summary." Revenues has three tables."

"Spreadsheet" = "Document"?

Or do you mean "My document has two Sheets, "Expenses" and "Summary"

What is "Revenues"? Another Sheet in the current Document? A separate Document?

Is your profile accurate? It lists Mac OS X v10.4.10, one update before the final one for v10.4. Unlikely to be a contributing factor, but possible.

I don't see a reason for the failure. I would suggest turning off 'Use header cell names as references' while building formulas/tables, but that may just be personal preference on my part.

Regards,

Barry

PS: Answers to the questions may help someone come to an AHA! moment before I get back to this.

B

Reply

Answer 2

Jul 3, 2012 5:14 AM in response to BGist

Also can you show the complete formula for the first two pictures in your previous post? select the cell with the yield sign, then triple click the focmula in the formula bar, copy, and paste into this forum:

e.g.:

D4=What ever the formula you paste

Reply

Answer 3

BGist Author

Level 2

270 points

Jul 3, 2012 5:42 AM in response to Barry

Sorry for the confusion, yes, I mean I have two "Sheets" inside of one "Document" ... and I'm trying to reference one "sheet" from the other.

Profile isn't accurate - I have 10.7.4 on an i7 MBP running Numbers09 (v 2.1.436).

I also should have mentioned, this file was originally built on Numbers for iOS, saved to iCloud ... copied to OSX ... saved back to iCloud ... and back and forth. It was on the 4th copy to OSX that it failed. This is particlarly suspicious, as the file is over 6MB, and has nothing but a few "sheets" of numbers. I can't see how it should be larger than 200k.

I've now turned off the "Use Header cells names as reference" option ... I didn't like that anyhow.

This "solved" the problem ... but ... I'd still like to understand why Numbers wasn't able to reference this one particular table ... or what made it different from all the others.

More notes to follow ...

Reply

Answer 4

BGist Author

Level 2

270 points

Jul 3, 2012 5:40 AM in response to Wayne Contello

Those were actually the full formula - I was showing a picture from the formula bar ... that was the weird part - as soon as I was typing the formula ... it just sorta ignored the fact that the reference was from another "Sheet"

Reply

Answer 5

Jul 3, 2012 10:07 AM in response to BGist

BGist,

It would help if you would go to Numbers Preferences and turn off the Header cell names as references option. Those references are nifty but can be confusing, especially considering that the reference seems to be the source of your troubles.

Jerry

Reply

Answer 6

Barry

Level 9

59,861 points

Jul 3, 2012 3:42 PM in response to BGist

"it just sorta ignored the fact that the reference was from another Sheet"

That's because of the use of Header cell names as references—there's only one column with a header cell name "Y1", so the Sheet name isn't necessary to identify the referenced cell. That's an example of the confusion that can arise from using the header cell names as references.

Regards,

Barry

Reply

Answer 7

Jul 3, 2012 8:03 PM in response to BGist

BG,

OI think the problem may be that Y1 is a valid Cell Reference in itself. When the compiler sees that, it may not consider the rest of your typed reference text as part of the address.

Using the Point and Click method of entering cell references seems to overcome this issue.

Jerry

Reply

Answer 8

BGist Author

Level 2

270 points

Jul 3, 2012 10:44 PM in response to Jerrold Green1

That's exactly the problem. I am using the point and click method. I'm hitting the "=" key, then clicking on the "Sheet" named "Expenses" and clicking on the cell B7 in the Table "Costs."

I dont know why its not returning the cell reference.

Reply

Answer 9

BGist Author

Level 2

270 points

Jul 3, 2012 10:48 PM in response to Jerrold Green1

This is the "solution" I'm following now ... but it only serves to reinforce my uncertainty regarding my continued use of Numbers. It does things I don't understand, and don't seem to be repeatable. On the iOS version, sometimes when I type "=" it starts a formula, other times it types the character "=" in the cell ... sometimes when I select the upper-left-hand corner of a table, it allows me to move it, other times, it enters the cell. Unless I can understand what causes Numbers to do the things I don't want it to do ... I don't see myself making any transition from Excel.

Reply

Answer 10

Jul 4, 2012 7:54 PM in response to BGist

BGist wrote:

This is the "solution" I'm following now ... but it only serves to reinforce my uncertainty regarding my continued use of Numbers. It does things I don't understand, and don't seem to be repeatable. On the iOS version, sometimes when I type "=" it starts a formula, other times it types the character "=" in the cell ... sometimes when I select the upper-left-hand corner of a table, it allows me to move it, other times, it enters the cell. Unless I can understand what causes Numbers to do the things I don't want it to do ... I don't see myself making any transition from Excel.

I can't help you with what is going on in the iOS version, and as far as I know, you don't have many spreadsheet program options there. As I mentioned above, I would stay away from the Header names as Cell References.

Jerry

Reply