Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

copying the @sum formula gives values in certain columns

Copper Contributor

This just started happening a couple of days ago  - and only on one workbook - but on multiple sheets. When I copy a simple @sum formula across columns, some of the columns calculate correctly, some of them show the value from the originating cell - any ideas for solutions? Thanks

15 Replies

Hi John,

 

Do you have absolute or relative references in your formula? And do you see in formula bar what the formula changes for new cell?

Sergei  - thanks for the reply - nope, its relative references and the auto recalculation is turned on, and its only certain columns that have this issue

All columns are formatted identically, no text formats in the middle?

Yes- just rechecked - all $ format - it only seems to be columns U - V - W - X - Y  - all other columns  work - and only on certian rows when I copy/past - its shown up on other sheets of the same workbook

It looks like you copied the sheet into another and some formulas keep references on initial sheet - something like this?

Could you show how the formula looks like for one of yellow cells and for another one with correct result (what do you see in formula bar)?

Here is the formula in the cell I am pasting from

 

=SUM(S10:S19)

 

and here is the formula in one of the yellow cells

 

=SUM(V10:V19)

 

 

And these formulas are in row #20?

yes they are - thanks

John, my last guess that's if you have circular references somewhere in calculations, not necessary in rows 10-19. It looks like numbers in row 12 are taken from row 7. If calculation for row 7 has circular reference, not necessary in the same sheet, sum for 10-19 won't be updated.

 

If that's not the case perhaps you may attach the file to check removing all sensitive information and sheets/numbers are not used. 

best response confirmed by john g (Copper Contributor)
Solution

Sergei - thanks - I will check for circular references and if that doesn't work, attach the files

Wow - that worked - a circular reference on another sheet that fed into the sheet that was giving me the problem - thanks so much

Glad to know, one more issue less in this life

yes - thanks again - I was not optimistic that this random error could be fixed

1 best response

Accepted Solutions
best response confirmed by john g (Copper Contributor)
Solution

Sergei - thanks - I will check for circular references and if that doesn't work, attach the files

View solution in original post