referencing cells in relation to named Cells

[partyof4]

Not sure if this is possible but have named cells a1:a8 named as test01


what i want to do is in formulas be able to use =countif(test01+ 4
colomns),"Test")

eg want to be able to use say e1:e8 in a formula but not have to use
references each time
have got a lot of formulas that use this range ?1:?8 and want to be
able to simplify the way the formulas work

can anyone help with this

PS using Excel 2003

Not sure if Iam going about this the right way but any solution would
be great

Thanks

Damien

 

[Biff]

You can try something like this:

=COUNTIF(INDEX(A1:E8,,5),"Test")

Where 5 refers to the column number of the range to count. In this case
column 5 is the range E1:E8. If you want to count A1:A8 then use column
number 1. The column number is RELATIVE to the indexed range.

To give it more flexibility you can use a cell to hold the column number and
then refer to that cell:

G1 = 5

=COUNTIF(INDEX(A1:E8,,G1),"Test")

If G1 is empty then the entire range, A1:E8, will be calculated.

Biff