referencing cells in relation to named Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

partyof4 - 11 Oct 2006 03:50 GMT

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

Reply to this Message

Biff - 11 Oct 2006 04:25 GMT

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

> Not sure if this is possible but have named cells a1:a8 named as test01
>
[quoted text clipped - 16 lines]
>
> Damien

Reply to this Message

Rate this thread: