Closed Thread
Results 1 to 5 of 5

Use formula result to specify row number in range

  1. #1
    Registered User
    Join Date
    03-28-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Use formula result to specify row number in range

    Hello,

    I have the following array formula.

    =SUM((MONTH('ADUNITS CSV'!$A$2:$A$29848)=MONTH($A15))*(YEAR('ADUNITS CSV'!$A$2:$A$29848)=YEAR($A15))*('ADUNITS CSV'!$G$2:$G$29848)*(ISNUMBER(SEARCH(B$1,'ADUNITS CSV'!$B$2:$B$29848))))


    Instead of inputting 29848 (as in $A$29848) manually, I'd like it to be the result of the following formula.

    MATCH("Totals",'ADUNITS CSV'!A:A,0)

    Basically I need to tell Excel that the last row number in the range I need is the result of the MATCH formula.

    I tried using the ADDRESS formula in combination with CONCATENATE as follows

    ="'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1) which results in

    'ADUNITS CSV'!$A$2:$A$29848 but Excel doesn't seem to interpret this result as a range, but as text so

    =MONTH("'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1)) results in an error.

    Any ideas?

    Thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Use formula result to specify row number in range

    I'd first make the point that using SUMPRODUCT is generally a little quicker than running a SUM CSE array... so from here on I will use SUMPRODUCT.

    To do what you want directly in the formula would necessitate either:

    a) cell:INDEX(column,MATCH(....)) approach
    b) INDIRECT

    The latter is Volatile so should be disregarded (IMO).

    Next, given you're using 29848 over and over you would need to repeat the MATCH calculation 4 times which is obviously inefficient... it woudl be better to store the MATCH result in one cell and refer to it from this formula such that is only calculated once... so let's assume you store the following in A1

    =MATCH("Totals",'ADUNITS CSV'!A:A,0)-1
    (you want to ignore the Row that contains TOTALS)

    You can then use:

    =SUMPRODUCT(--(MONTH('ADUNITS CSV'!$A$2:INDEX('ADUNITS CSV'!$A:$A,$A$1))=MONTH($A15)),--(YEAR('ADUNITS CSV'!$A$2:INDEX('ADUNITS CSV'!$A:$A,$A$1))=YEAR($A15)),--(ISNUMBER(SEARCH(B$1,'ADUNITS CSV'!$B$2:INDEX('ADUNITS CSV'!$B:$B,$A$1)))),'ADUNITS CSV'!$G$2:INDEX('ADUNITS CSV'!$G:$G,$A$1))

    You could think about using Dynamic Named Ranges also but these too can bring calc overheads...

    Does that help at all ?

  3. #3
    Registered User
    Join Date
    03-28-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Use formula result to specify row number in range

    That worked! Thanks very much. I learned a bit more about Excel in the process .

  4. #4
    Registered User
    Join Date
    07-30-2010
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Use formula result to specify row number in range

    Hi, I have a similar problem that I am hoping I can find the answer to:

    I frequently use something like this for MINIF or MAXIF:
    ={MAX(IF($F$25:$F$40=F16,$G$25:$G$40))}

    It works just fine. But now lets say I want to compare $F$25:$F$40 to a range of possibilities:
    ={MAX(IF($F$25:$F$40=F16:H16,$G$25:$G$40))}

    Again, this works just fine (finds the highest numeric value in $G$25:$G$40 that corresponds to any value in $F$25:$F$40 equal to either F16, G16, or H16, which are non-numeric)

    But suppose that sometimes I don't want to compare $F$25:$F$40 to to F16:H16, but instead want to compare $F$25:$F$40 to F17:H17, or 18, 19, etc, and I can get that row number with a simple MATCH. How do I plug the result of the MATCH in for the row number:
    ={MAX(IF($F$25:$F$40=F[Result of Match]:H[Result of Match],$G$25:$G$40))}

    I hope that makes sense - it's a greatly simplified example.

    Thanks!

  5. #5
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Use formula result to specify row number in range

    Hi ajhr
    Welcome to the forum but,
    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Please revise the forum rules.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1