1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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.

Thread Status:
Not open for further replies.
Advertisement
  1. HOBOcs

    HOBOcs Thread Starter

    Joined:
    Jan 5, 2004
    Messages:
    7,461
    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

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    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

    HOBOcs Thread Starter

    Joined:
    Jan 5, 2004
    Messages:
    7,461
    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.

    (y)
    Much appreciated Andy

    Marking solved.
     
  4. bomb #21

    bomb #21

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

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/452961