Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

HOBOcs's Avatar
HOBOcs   (Jim) HOBOcs is offline   HOBOcs has a birthday soon!
Member with 7,303 posts.
THREAD STARTER
 
Join Date: Jan 2004
Location: Markham ON Canada
25-Mar-2006, 05:44 PM #1
Cool 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.
bomb #21's Avatar
Member with 8,263 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
25-Mar-2006, 07: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 08:10 PM..
HOBOcs's Avatar
HOBOcs   (Jim) HOBOcs is offline   HOBOcs has a birthday soon!
Member with 7,303 posts.
THREAD STARTER
 
Join Date: Jan 2004
Location: Markham ON Canada
25-Mar-2006, 08: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.
bomb #21's Avatar
Member with 8,263 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
25-Mar-2006, 08:42 PM #4
You may find the other option more suitable in respect of "used in a number of different calculations". Either way, enjoy.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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


(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑