deleting or adding rows suddenly changing formula links

Question

Level 2

380 points

deleting or adding rows suddenly changing formula links

i have quite a large spreadsheet (Numbers v3.2.2) with a number of different sheets, with many formulas that link between the sheets.

there's one key main sheet that contains most of the information which i regularly edit and update. all of a sudden, i found that if i add or delete a row in this sheet, it throws out all the formula links for the other sheets.

so, for example, if a formula from another sheet took the value from my main sheet (say from Row 10), if i added a row to the main sheet around Row 5, the other sheet’s formula now links to the row above (still Row 10), but not the same contents (that are now in Row 11).

another strange thing too - when checking to see which formulas Preserve Rows etc, i found that some do, some don’t (but this shouldn't really make a difference when adding or deleting rows), but i’ve found that although formulas show the correct content (say a number or title), when i select the cell in the formula, it actually links to a blank cell (above or below the target cell). very odd!

this hasn't happened before and it only seems to effect rows after around 180.

also, if I then Undo the insertion or deletion of a row, the formulas in the other sheets still link to the incorrect row (the one either above or below depending if a row was added or deleted). i have to close the file without saving to ensure it doesn’t mess up my whole sheet.

has anyone experienced this or seen this happen before… can anybody please help/advise?

is this file corrupt in some way and is there a fix?

cheers, Ric.

MacBook Pro (15-inch Early 2011), Mac OS X (10.7.3)

Posted on Jul 6, 2015 1:18 AM

Reply

Answer 1

SGIII

Level 8

35,288 points

Jul 6, 2015 7:28 AM in response to ric frankland

It's hard to tell without screenshots, but from your description it may be that you are trying to use Numbers in a way that it isn't really designed to be used.

In general you shouldn't have many different formulas linking between tables. Often you have one or two formulas per table, which you then fill into adjacent cells of that table.

Preserve Row / Preserve Column anchors a reference for when you fill a (same) formula into adjacent cells. It doesn't mean that the formula will always point to a specific cell that you move around in your document.

It may be helpful to have a look at the templates at File > New in your menu.

SG

Reply

Answer 2

ric frankland Author

Level 2

380 points

Jul 6, 2015 9:50 AM in response to SGIII

thanks for the response SG, but i'm not sure i agree with your comments about how Numbers should be used... i've found it really very powerful to connect/link data from one sheet to another... it's performed really well and i suspect this problem is just a glitch of some sort, or i've done something very stupid!

i would send you some screenshots but i really don't think that will show anything or help - i've now also discovered since my first post that the formulas in one sheet now point to the wrong cells in the main sheet and yet still show the correct information. it's just very odd.

adding or deleting the rows in the main sheet just seems to tell the other sheets which row it should actually be picking up, and therefore it's wrong.

one option is to go through and drag each one to the correct cell, but with there being soooo many, it will take forever :-/

Reply

Answer 3

SGIII

Level 8

35,288 points

Jul 6, 2015 10:08 AM in response to ric frankland

Without more any specifics it's very hard to know what's going on with your document. Good design (usually involving a relatively small number of formulas, as you'll see in the templates) can avoid a lot of mysterious glitches. BTW, values in Numbers are contained in cells in tables. Formulas typically refer to ranges of cells in a table (not sheets). This distinction may sound pedantic, but it's critical to understanding how Numbers works. In your menu visiting Help > Numbers Help can be useful.

SG

Reply

Answer 4

Hiroto

Level 5

7,461 points

Jul 6, 2015 10:55 AM in response to ric frankland

Hello

I suspect your document is corrupt. I'd try exporting it as Numbers v2 (Numbers '09) document and import it back as Numbers v3 document and see if it fixes internal corruption.

Good luck,

H

Reply

Answer 5

t quinn

Level 5

7,043 points

Jul 6, 2015 3:51 PM in response to Hiroto

Hi Hiroto,

I think this is a very good suggestion.

quinn

Reply

Answer 6

SGIII

Level 8

35,288 points

Jul 6, 2015 6:20 PM in response to t quinn

Either corruption or the normal Numbers 3 "sorting" behavior (the op reports deleting rows, inserting rows, moving cells around, etc.). That will throw off references that were intended to be anchored to a specific cell and follow that cell wherever that cell goes.

SG

Reply

Answer 7

t quinn

Level 5

7,043 points

Jul 6, 2015 10:19 PM in response to SGIII

Hi SG,

I get your point. I have arranged my tables so I never have to sort and the data gets pulled based on the data itself or index columns. You get your ways of doing things and forget how it can be.

quinn

Reply

Answer 8

ric frankland Author

Level 2

380 points

Jul 11, 2015 6:40 AM in response to SGIII

thanks everyone!! sorry for delayed response... spent the last week crying... ;-)

actually, it wasn't as bad as i first thought, thanks to my multiple backups on Apple's Time Capsule... i managed to figure out exactly which day the problem occurred and restored the version from the day before. thankfully only a few days work was lost.

i did try to save as the v2 file version Hiroto (good idea), but it retained the problem, just showing how none of the correct linked cells were correct.

it seems the file may have been corrupted, just as SG said. it's kind of good to know that there have been similar issues reported, so that it's not just something I've done or a problem with my system. i love the software, and although i have a huge file (8.4MB, just data), it works great. i'm hoping this problem is just one of those things that happens from time to time and not that i'm hitting the limits of the software as SG first suggested......? although i don't think it's going to get a whole lot bigger, i do intend to keep using the file for a few years to come.

i think there's a couple of things i'll do in the future... one, not keep the file/app open the whole time, something i've tended to do, and then add content or tinker with it from time time - it's possible that if there was an issue with my system, it could have had an impact on Numbers....??

also, i'm also going to regularly check that all is in order and cells are sorting/linking properly, especially after inserting, deleting or moving a large number.

thanks again everyone, and any other tips would be very much appreciated!

Reply