formula locking?

[mpenkala]

Hi there,

I've run into a problem. I currently am building a stats page for a
basketball team. I get reports after each of there game, so what I've done
is build a master sheet that imports the data from each report they give me.
Everything was working fine but I've notice one little thing that's got me
stumped.

An example of one of the reports I get. It's broken down in quarters, and
Play-by-Play. So on Sheet1 I have a play by play of the quarter. It is
usually quiet messy but I've created a nice macro that cleans it up and
removes blank rows and such. The problem is that I have formulas on Sheet2
that read the info on sheet1 and when I run my macro to remove the blank
cells, the formulas on Sheet2 change.

Example:
On sheet 2 i have the following formula:

=COUNTIF(PbyPQ1!$A$1:$A$150,"Matt Penkala - Inside Shot:Made*")

On sheet one, anytime Matt Penkala scored, it show's as "Matt Penkala -
Inside Shot: Made". This works good. But the range is what's messing up.
Say my macro removes 18 blank rows, then the range changes from A1:A150 to
A1:A132.

Any ideas? I've read about unlocking the cells on Sheet1, but that doesn't
seem to do it either. So what I guess I'm really looking for is a way to
lock the range in a formula.

Thanks,
Matt

 

[sb1920alk]

Reference the entire column instead of just a piece of it: Replace
$A$1:$A$150 with A:A, or $A:$A.

 

[willy]

Also take out the $
this is to make it an absolute reference rather than relative (moving).
experiment with this a little, it behaves differently with how you access
the cut and insert commands, with macros I am not sure.

 

[mpenkala]

Hi,

I think this will work. Thanks!

Matt

Reference the entire column instead of just a piece of it: Replace
$A$1:$A$150 with A:A, or $A:$A.