CountIf formula problem when referencing a column.

[Shawn]

I am referencing a column from a seperate sheet within one file. The problem
I have is that when I insert a column my formulas are not updating properly
on the seperate sheet. An example of my formula is
"countif(Sheet2!F$3:F$500,B11)". I need the formulas to find the critera one
column at a time. When I need to insert a column on sheet 2 the column
numbers referenced in the formulas change from A,B,C,D,etc to A,B,D,E etc.
Basically, as I drag and copy across a range with a formula the columns
originally format properly within the formula but, when I go back and insert
a column on other sheet the formulas will not automatically update correctly.

 

[Dave Peterson]

Actually, your formulas are adjusting perfectly.

I would think that you'd still want the formula that used to point at a certain
field to always point at that field.

If column F of sheet2 was the department number for some employees and B11 was a
single department number, then if you insert a new column before column F of
sheet2, you'd still want to look at that department number column (now moved to
column G).

Wouldn't you?

 

[ckemler]

You should consider naming your ranges. Search the help under "name" or
"range". Taking the extra time to do this when you set up your file should
take care of this.

 

[Shawn]

This is true, however when I insert a column on page 2 then the column
reference that my formulas need on page 1 don't recognize the new column.
The forumals will read across the row and go F,G,H,I,K,L,M, ect. As you can
see the physical column on page 2 does exist however column J is not
recognized within the page 1 formulas that already exist. The formulas will
not correct themselves automatically. I have to go back to page 1 and drag
across the range from the first cell to update.

 

[Shawn]

I will give this a try and see what happens. I will let you know.