# Solved: Excel - reference a value (contents of cell) for use in dynamic part of range

Discussion in 'Business Applications' started by HOBOcs, Mar 25, 2006.

Not open for further replies.
1. ### HOBOcs Jim Thread Starter

Joined:
Jan 5, 2004
Messages:
7,477
Excel question:

Testing - I have a column of numbers where I do a countif (A10:A365,A1)
In this case A1 = "1" and the column contains various number from 1-10.
I want to count the number of times 1 appears in the column up to row 100

I want to substitute "A365" with a value from cell "A2" ie, A2 = 100 and make it A100
So I'd like the countif logic to be countif(A10:A100, A1)

Then if I change A2 to 150 I get the range from A10:A150.

My issue is this range is used in a number of different caluclations and I'd like to dynamically change the rows to search.

Any help/direction appreciated.

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,284
Try this in place of your original COUNTIF:

=COUNTIF(INDIRECT("A10:A"&A2),A1)

(the section "A10:A"&A2 builds a literal string that "responds to" A2 value ; then you just throw in INDIRECT to make it work "for real")

Rgds,
Andy

EDIT: you may prefer a different approach:

=COUNTIF(var_Range,A1)

, where the defined name var_Range refers to:

=OFFSET(Sheet1!\$A\$10,0,0,Sheet1!\$A\$2-9,1)

You can test the dynamic range "effect" by entering 12 in A2, pressing F5, typing var_Range and pressing Enter. Then repeat with 15 in A2. Etc., etc.

3. ### HOBOcs Jim Thread Starter

Joined:
Jan 5, 2004
Messages:
7,477
Perfect exactly what I was looking for.

Used the "=COUNTIF(INDIRECT("A10:A"&A2),A1)"
I'll check out your other tips later but this has got me going.

I was thing about string manipulation but thought there might be an easier way.

Much appreciated Andy

Marking solved.

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,284
You may find the other option more suitable in respect of "used in a number of different calculations". Either way, enjoy.

As Seen On