• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculated column changes formula when pasting into table.

A

aggie81

Member
I have a table in Excel 2010 with the following in column C


=IF(A834<>"",COUNTIF($A$2:A834,A834)&" OF "&COUNTIF([ItemNo],A834),"")

When I paste a new block of items into the table it changes the formula to


=IF(A835<>"",COUNTIF($A$2:A842,A835)&" OF "&COUNTIF([ItemNo],A835),"")


I recently upgraded from 2007 and didn't have this problem.

Does anyone know why and how to fix it?

Thanks,

Lee
 
I assume your problem is the range A834 changing as A835, when only a single cell in dragged,


Why not make A834 constant with $?

If you using Table why dont you give reference to entire column (similar to as [ItemNo]

Check if any macro is deleting or adding rows.

Check the Name manager if this happens to make any range
 
Hi,


Change your formula with constant by pressing F2 then press F4 on each reference.


=IF($A$834<>"",COUNTIF($A$2:$A$834,$A$834)&" OF "&COUNTIF([ItemNo],$A$834),"")


Thanks,

Suresh Kumar S
 
Thanks to both of you for the suggestion.

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 1 OF 7 60

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 2 OF 7 90

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 3 OF 7 120

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 4 OF 7 120

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 5 OF 7 120

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 6 OF 7 60

I4S089 4" IMPATIENS-SUPER ELFIN BR. ORANGE 7 OF 7 60

The formula needs to stay the way it is because as more lines are added, the formula counts the crop number and the number of crops. If I make the range absolute or reference the column then I get something like 7 of 7 all the way down or 3 of 3 depending on how many of the same item in column A.

I opened the workbook in Excel 2007 and it works fine adding rows at the end of the table. In Excel 201o, it changes the cell reference. Why?

Thanks again.

Lee
 
https://www.dropbox.com/s/9h48axi9oqte8c6/Production%20Schedule%202012%20Uploaded.xlsx

I hope the link above works to the file. I removed and edited so all should work. I am using 2010 when I get the problem. When the file is opened in 2007 it works as normal.

The # of Crops column is what gives me the restore to calculated column message

Thanks,

Lee
 
You must log in or register to reply here.
Back
Top