cell range referencing from within a function

[Guest]

hi,

I have a cell which counts instances of a word in a range. The formula for that is:

=COUNTIF(D529:D541,$F$5)

F5 contains the word i'm counting

This works fine, giving me the number of times the word in F5 appears in the given range. However, I am adding new data to this constantly and I want to be able to copy this formula easily. Basically I want to put "D529:D541" in a cell, say L10, and then reference this cell somehow from the formula. (sort of like, =COUNTIF(ref.L10,$F$5).

Anyone know how I can do this fairly easily? I've looked at CELL("contents", L10), but it doesn't work when placed in the COUNTIF function (I think it doesn't return type range or something).

All help appreciated,

Lobo

 

[lobo]

odd, it logged me out while I was typing that..

here's my login if that's needed

Lobo

 

[Aladin Akyurek]

On 2002-03-14 02:30, Anonymous wrote:
hi,

I have a cell which counts instances of a word in a range. The formula for that is:

=COUNTIF(D529:D541,$F$5)

F5 contains the word i'm counting

This works fine, giving me the number of times the word in F5 appears in the given range. However, I am adding new data to this constantly and I want to be able to copy this formula easily. Basically I want to put "D529:D541" in a cell, say L10, and then reference this cell somehow from the formula. (sort of like, =COUNTIF(ref.L10,$F$5).

Anyone know how I can do this fairly easily? I've looked at CELL("contents", L10), but it doesn't work when placed in the COUNTIF function (I think it doesn't return type range or something).

All help appreciated,

Lobo

Lobo,

What you are looking for is:

=COUNTIF(INDIRECT($L$10),$F$5)

Aladin

 

[lobo]

thanks that worked perfectly

I think it should probably come up when you search for referencing cells in excel help...

thanks again for the help

Lobo