keep cell range constant when deleting rows

[Si902]

Hi all

This will probably be really simple but thanks in advance for your time and help.

i have a COUNTIF formula with a cell range for example P5:P250, however, i need to delete rows from within this range, when i do this the range drops, eg P5:P249

Is there a way to keep the defined range constant, i seem to recall something about dollar symbols $ but have tried adding these however no joy.

Thanks

Si

[johnjohns]

I hope INDIRECT command will help.

rgds

johnjohns

[Si902]

Hi john

Thanks, do you mean just replace COUNTIF with INDIRECT ?

Ta

Si

[johnjohns]

see the attachment

rgds

johnjohns

[DonkeyOte]

If you prefer to avoid Volatility use INDEX rather than INDIRECT

=COUNTIF(INDEX(P:P,5):INDEX(P:P,250),"apple")

(assumes column P is never removed in it's entirety)

[Si902]

Thanks for that John, but dont think its quite what i'm after, i probably didnt describe it well enough, here's another try

i have a sheet pulling data from other worksheets within the same workbook, the formula im using is COUNTIF(A!P5:P250,"Y") this returns the number of Y on sheet A in that cell range, however i need to delete rows on sheet A but need the cell range on the data sheet to remain the same.

Ta

[Si902]

Thanks DonkeyOte

Will try.

Si

[DonkeyOte]

There's no issue with johnjohns approach other than the fact INDIRECT is Volatile which may / may not be an issue for you
(see link in sig. for more info. re: Volatility)

=COUNTIF(INDIRECT("'A'!P5:P250"),"Y")

will work equally as well as:

=COUNTIF(INDEX('A'!P:P,5):INDEX('A'!P:P,250),"Y")

(INDIRECT is pref. in such instances where intention is to physically delete & replace 'A' sheet - if not I tend to avoid it)

[Si902]

Have tried that DonkeyOte however returns #NAME

I added m sheet name A into the formula though

=COUNTIF(INDEX(A!P:P5,5);INDEX(P:P,250),"Y"

Any suggestions?

[DonkeyOte]

see the prior post for working examples of both approaches...

[Si902]

Just seen your other reply

Will try

[Si902]

Neither seem to work

=COUNTIF(INDIRECT("A'!P5:P250"),"Y" returns #REF!

=COUNTIF(INDEX('a'!P:P5,5):INDEX('A'!P:P,250),"Y" returns "missing parenthasis" when added following 250)) returns "entered too few arguments" and #NAME?

[DonkeyOte]

Not to sound like a broken record - use the samples provided (and/or compare the parentheses in my suggestions to those you have tried)

[Si902]

Sorted

Thanks for that DonkeyOte

I used the example provided and works a treat, also realised what i had missed out.

Thanks again

Si