SOLVED

copying the @sum formula gives values in certain columns

Copper Contributor

copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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?

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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)?

Re: copying the @sum formula gives values in certain columns

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)

Re: copying the @sum formula gives values in certain columns

And these formulas are in row #20?

Re: copying the @sum formula gives values in certain columns

yes they are - thanks

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

Glad to know, one more issue less in this life

Re: copying the @sum formula gives values in certain columns

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

Re: copying the @sum formula gives values in certain columns

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