Search Search for: Business ApplicationsAll Forums

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

 HOBOcs   (Jim)
Member with 7,158 posts.

Join Date: Jan 2004
25-Mar-2006, 04:44 PM #1
Solved: Excel - reference a value (contents of cell) for use in dynamic part of range
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.
__________________
HOBOcs - Experienced in problem investigation and resolutions..... on the prowl once again!!
Member with 8,249 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
25-Mar-2006, 06:54 PM #2
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.

Last edited by bomb #21; 25-Mar-2006 at 07:10 PM..
 HOBOcs   (Jim)
Member with 7,158 posts.

Join Date: Jan 2004
25-Mar-2006, 07:34 PM #3
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.
Member with 8,249 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
25-Mar-2006, 07:42 PM #4
You may find the other option more suitable in respect of "used in a number of different calculations". Either way, enjoy.
 techguy.org/452961
As Seen On

WELCOME TO TECH SUPPORT GUY!

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)