moving contents of cell w/out changing related formula

  • Thread starter Thread starter phil1ray
  • Start date Start date
P

phil1ray

I have a worksheet with names I move around. I created a countif forml
to count if a cell or a range of cells have text. I drop and drag th
names but the formula relating to the cell changes to match the ne
range. How can I just move text and not change the range in th
formula
 
I think this is what you want.

Say your range that you're checking is B9:E47.

Instead of using:
=COUNTIF(B9:E47,"Phil")
You can use:
=COUNTIF($B$9:$E$47,"Phil")

Then when you drag this formula, the $b$9:$e$47 won't change.

The $ sign means to not adjust that part of the address (Column or Row).

You can find more info in excel's help.
Look for "About cell and range references"
 
Dear Mr. Peterson,

I appreciate your help, but I think you misunderstood my problem.
I don't want to move the cell containing the formula to move. I
draging and droping the cell the formula relates to.

Exmple: COUNTIF($J$33,"*")
When I drag and drop cell J33 to H33 the formula changes to
COUNTIF($H$33,"*"). I would like the formula to stay the same
and return an answer of 0 indicating no persons are in that
area.

Thanks for your help!
 
I posted a similar question on this board last month. Here is the lin
to that thread:

http://www.excelforum.com/showthread.php?s=&threadid=180014

About halfway down you will see references to using the INDIREC
function. That is what you want to use to prevent your formula fro
re-writing itself when you drag (or cut-and-paste) a referenced cell t
a new location.

So your example formula will look something like:

COUNTIF(INDIRECT("J33"),"*")

Then when you drag J33 to H33, your formula will still reference J33
If you are checking a range of cells:

COUNTIF(INDIRECT("A33:J33"),"*")

is the same as

COUNTIF(A33:J33,"*")

except that it will always refer to A33:J33 even if you drag o
cut-and-paste cells within the range of A33:J33
 
Back
Top