You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Replacing formlara (eg in a Row) is only working on a single cell. Replacing the entire row will fail. the reference is shifting after the first cell to some other value, makes it impossible to work with larger sheets.
it was working before.
That, what it should look like (after pasting every single cell)
$B337×E$332÷365×E$56 $B337×F$332÷365×F$56 ……
This is what it looks like (after pasting all cells)
$B337×E$332÷365×E$56 $B337×F$303÷365×F$142 $B337×G$303÷365×G$142
Looks like you found a new bug. It shouldn't do what it is doing even with Categories on.
Instead of copy/paste, you can drag-fill to the right to fill formulas into the other cells in the row. That seems to work with Categories on.
Now that I know Categories was on, I am able to reproduce the problem. In the spreadsheet shown below , I copied B4 and pasted to C4:E4. The cells with the triangles are all reference errors in the formula.
If I turn off categories, Copy cell B4, turn on categories, then Paste to C4:E4 it works as it should and continues to work correctly after that until I add another "a", "b", or "c" to column A, then it messes up again (though differently, as shown below). I can add a "d" to column A without it messing up but I think it would mess up if I had more rows of data in category d and was referencing one in the middle.
Looks like you found a new bug. It shouldn't do what it is doing even with Categories on.
Instead of copy/paste, you can drag-fill to the right to fill formulas into the other cells in the row. That seems to work with Categories on.
Now that I know Categories was on, I am able to reproduce the problem. In the spreadsheet shown below , I copied B4 and pasted to C4:E4. The cells with the triangles are all reference errors in the formula.
If I turn off categories, Copy cell B4, turn on categories, then Paste to C4:E4 it works as it should and continues to work correctly after that until I add another "a", "b", or "c" to column A, then it messes up again (though differently, as shown below). I can add a "d" to column A without it messing up but I think it would mess up if I had more rows of data in category d and was referencing one in the middle.
In this screen shot, the formula shown in in cell is on cell L20 (selected cell)
The formula, as written,
As the formula is filled right, into the rest of the cells in row 20, the Column in $B20 will not change, because the Absolute Reference operator ($) has been placed before it to lock that reference onto column B, and the columns in the other three references will increment by one letter to match the column in which the new copy of the formula is pasted (or is filled into.
As the column is filled right into the rest of the cells in row 20, the Row numbers in the four references will not change, as all copies of the formula are in the same row.
As the formula is filled down into rows below, the column references will not change, because each copy of the formula is in the same column as the one it is a copy of.
But as the formula is filled down into the rows below, the row numbers in three of the references ($B20, L8 and L45) will increment by 1 for each row the formula is filled down. The second reference, L$7, the Absolute reference operator preceding the 7 will keep the reference locked onto the Cat 1 value in row 7 of the column containing that copy of the formula.
Changes needed:
Assuming all of the cells in rows 17 to 23 will reference the cell in column B that is in the same row as the formula, the $B20 reference in row 20 needs no changes to keep it correct in other rows.
To keep the L8 reference locked on row 8, change L8 to L$8
If the row 48 reference is to the same row, but different columns for all copies of the pasted formula, insert $ before the number 48. If the reference is to be to the same cell (L48) in every copy to the formula, include the $ before the column letter and the row number ( $L$48)
Regards,
Barry
PS: Please try to include the Column and Row reference tabs in screenshots. Makes it much easier to locate the cells referenced in the formulas.
B.
Going by how you explained it in your post with the screenshots where it seems like you are copy/pasting the correct things, I'd say it is corruption. If it is corrupted it would be best to quit using it. Unfortunately we have no way of telling what went wrong and no way to look at the inner working of a file to correct anything even if we knew what it was.
You could try one of the following:
I don't know if any of those options will work to clean it back up or how much effort this will be for you.
thanks for the replies, here is what I did so far:
Working with the sheet was never a problem before. I assume, since the last update (I don't know, when it was applied), this issue is here. To me, it looks like a bug, because the way I copy and past the formulas is quite common.
Also, I tried to copy and paste entire rows (270 cells long) and it works. all references were adapted correctly.
Ok, here some screenshots:
The formula in the marked cell should be copied into the remaining cells of that row.
the formula refers to cells like shown here. The blue marked remains, while cells in the CAT 1, CAT2 rows follow the cell.
After copying the formula into the marked cells, the reference to the cat1 cat1 row is lost.
the formula is broken
only in the first cell copied into, the result is still present, for the next cells, the reference is out of nowhere. Please note, the first screenshot shows a working sheet. I just copied the same formula via copy / paste in more than one cell.
If I paste the formula into a single cell, it is working. If I take 2 cells holding a pasted formula, I can copy and paste those together. These, I can take the 4 and copy / paste, take 8 ...16....
I can't say exactly, when its startet, but at least, two weeks before, it was working.
this is quite a pain, if you have to replace cell is large rows.
What do you mean by "replacing formula"?
To post screenshot, shift-command-4, select area with cross-hairs, release, start new post here and use the "mountain-and-moon" icon 'Image Insertion' icon below the compose window to insert the screenshot from the Desktop.
SG
I think you are asking about starting with a formula in a particular cell, then filling that formula to other cells in the same column (or, maybe the same row).
from your post,
That, what it should look like (after pasting every single cell)
$B337×E$332÷365×E$56 $B337×F$332÷365×F$56 ……
you should probably make sure you are clear on the meaning of the cell references...
$B337 is a reference to cell B337 AND the column "B" is absolute while the row, 337, is relative. This means that when that reference is copy and pasted, that the cell reference $B337 will be adjusted based on the different in rows and ignoring the difference in columns (because the column reference is absolute).
I think it will be MUCH easier for us to propose a helpful solution if you post a screenshot of the table you working on and then describe what outcome you want, OMITTING HOW YOU THINK IT SHOULD BE SOLVED.
I think for the cell you have selected, you should use the formula:
=$B20*(L$7+L$8)*L$48
the suggestion makes the column for B20 remain column B even if you fill the formula to the right (or left).
the suggestion makes the row for L7 remain row7 and L8 remain row 8 while allowing the column to adjust when filled left (or right)
to fill the formula across the row, select the cell, copy
the select the "other" cells of the row where this formula should go, paste
What you said it is doing is not something I have ever heard of Numbers doing. Either there is some kind of strange corruption in your document that has never been seen in before in this forum (which could be the case, anything is possible) or you copied the wrong/incorrect formula and are pasting it into the row. My suggestion is to start with the more likely one. Are you sure you copied the formula from the first cell and that you are not accidentally pasting something else that you already had on the clipboard?
Finally, I found a workaround to this issue:
I used Categories in my sheet to organize it a little. After removing the Category, I can pst formulas as I want. If I create a new category, the problem is back.
This is nothing, that can be solved by the user. It was point it the software engineering, after it was working online. Hopefully, this page is monitored by apple too.
Udo
As I posted above, it would help to post a screenshot.
You are of course always welcome to post how you are trying to solve your problem!
SG
There are some pretty good mind readers here. From the information you provided I can see how no one might have guessed you had Categories turned on!
SG
But anyhow, I used categories quit a lot, it didn't cause any problems 'til now. I should have mentioned it before, but ....
Hopefully, there is an update coming soon to correct this bug.
Replacing formulas issue
