SOLVED

# 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 # 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