moving contents of cell w/out changing related formula

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
 
D

Dave Peterson

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"
 
P

phil1ray

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!
 
J

JanetW

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
 
Top