Insert Formula

Question

Level 1

144 points

Insert Formula

I have a spreadsheet that has cells that refer to cells on another tab. My first question is, when I double click on a cell it does NOT put me into formula edit mode. I can go to Insert then Formula and I can create the formula. Is there a state that I am in that does not allow the double click to put me in edit formula mode?

Second my cell is pointing to a table on another tab. I create the cell and it displays the correct value (i.e. the value from the other tab) in this case Sep. But the cell's formula does not contain the tab name. I attached

I think that the formula should be Sep::Table 1-1-1::C2. So why does it have the right value? Why doesn't the cell reference have the month qualifier in front of it?

Thanks for your help.

OS X Yosemite (10.10.4)

Posted on Oct 7, 2015 6:35 AM

Reply

Answer 1

Oct 7, 2015 6:56 AM in response to MD_Gene

To edit a formula, you double click the cell, then edit in place:

A formula does not require the Sheet or table name if the reference is unambiguous .

If there is a reference to Sheet "Sheet 1", table "Table 10" cell A5

and there are no other sheets with a table named "Table 10" then all you need to refer to any cell in "Table 10" is:

"Table 10::A5"

Reply

Answer 2

t quinn

Level 5

7,043 points

Oct 7, 2015 7:07 AM in response to MD_Gene

Hi MD_Gene,

You should try quitting Numbers and perhaps restarting. Maybe that will adress the double click issue.

If you have unique table names (and I suspect Table 1-1-1 is unique) Numbers can find the table in your doc without a sheet desigination. If there were two Table 1-1-1's in your spreadsheet Numbers woud automatically show the sheet/tab.

quinn

Reply

Answer 3

MD_Gene Author

Level 1

144 points

Oct 7, 2015 7:13 AM in response to Wayne Contello

That's great to know. That is the ambiguity issues. So I assume, when I create another tab, call it Oct with the same table name Numbers will resolve the ambiguity?

But for some reason double click is NOT putting me into formula edit mode. If I double click on a non blank cell it puts me into formula edit mode. But if I double click on a blank cell nothing happens. Is there a setting I am missing?

Reply

Answer 4

t quinn

Level 5

7,043 points

Oct 7, 2015 7:19 AM in response to MD_Gene

Hi MD_Gene,

The double click issue is mysterious to me. That is why I suggested the restart. Numbers seems to be experiencing a little glitch.

Having a table with the same name in different sheets should mainifest the sheet names in the related formulas.

quinn

Reply

Answer 5

MD_Gene Author

Level 1

144 points

Oct 7, 2015 8:01 AM in response to MD_Gene

Thanks. The unambiguous reference makes sense.

But here's the problem I am having with double click: https://youtu.be/7yuvjqvwSBE

Reply

Answer 6

MD_Gene Author

Level 1

144 points

Oct 7, 2015 8:44 AM in response to MD_Gene

I figured it out. Numbers allows one to edit a blank cell by typing, in this case "=". Sorry for the confusion but I am still on a learning curve and sometimes slip back down the slope.

Reply