+ Reply to Thread
Results 1 to 10 of 10

Need INDEX function to create dynamic range from last row of data upwards a number of rows

  1. #1
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Need INDEX function to create dynamic range from last row of data upwards a number of rows

    I'm looking for a less volatile formula for creating a dynamic range.

    Presently I have a need to create a dynamic range to chart data where the dynamic range is the last row in a column with data upwards a number of rows based on a cell value

    For example if the data were in column A starting at row 10 and the last cell in column A with data is 100 and the number of rows upwards is 20, I would then require a dynamic range that encompasses A81:A100.

    I have used the formula below to look in column D between D39 to D1000 and it returns the correct range even though i may only have data to row 300 say, however it is extremely volatile. cell H34 contains the value for number of rows upwards from the last cell of data.

    OFFSET(Sheet1!$D$39,SUM(--(LEN(TRIM(Sheet1!$D$39:$D$1000))>0))-1,0,-MIN(Sheet1!$H$34,SUM(--(LEN(TRIM(Sheet1!$D$39:$D$1000))>0)))+0,-1)
    The research I have done shows that an index function would be more stable, but how would one go about creating a dynamic range per my example above.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,918

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Please Login or Register  to view this content.
    count vs. Counta??
    H:H --> i.e. whatever the last column is...
    Ben Van Johnson

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,012

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    You'd need something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Thanks for the responses.

    I don't think the count function will work as my data that i will be working on starts at row 38 with a column heading and row 39 onwards for actual data.

    I did try using something similar to the 2 suggestions but we end up capturing the summary data that is above row 38 which then gives a false range.

    Thanks

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,684

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Try

    =SUM(INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)-19):INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)))

  6. #6
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Sorry guys none of the solutions appear to be working.

    I have attached a workbook where i have data that is charted. The named range "test" contains the latest formula offered by Mr. Topley. When the value in cell B35 is changed the range in then named range "test" ought to change from the last cell in column a back 5 rows so rows 53 thru 49 are charted, when value of B35 = 5.

    A second chart i added with name "Offset Formula", this has a named range "test_2" for the chart series which uses the formula is outlined in my initial post, in this case when changing the value of B35 the range is adjusted accordingly in the chart. But as i said earlier the offset function is really volatile and i need a substitute for my named range formula that would be less volatile.

    Thanks for your assistance
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,684

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Try

    =SUM(INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)-$B$35+1):INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)))

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,918

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 05-14-2017 at 04:03 PM.

  9. #9
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    My apologies to all, I'm just getting back to my project.

    The good news is that my problem is solved. I am now able to chart the correct ranges using the suggestion of protonLeah, thank you kindly.

    I was also able to use the suggestion from Mr. Topley, this was used to do calculations within my worksheet which replaced a sum calculation using the offset function, again thank you kindly.

    Thank you all for your contributions

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,012

    Re: Need INDEX function to create dynamic range from last row of data upwards a number of

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create dynamic variable range to use in function
    By olli.excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2017, 07:24 PM
  2. [SOLVED] create chart using dynamic range (Indirect Function)
    By umbata in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-08-2015, 10:37 PM
  3. Replies: 6
    Last Post: 09-09-2014, 02:25 PM
  4. using offset function to create dynamic range based on a match search
    By freddiethomas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2013, 04:14 AM
  5. Replies: 1
    Last Post: 10-05-2011, 03:18 PM
  6. NUMBER OF ROWS IN A DYNAMIC RANGE
    By Larry in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-14-2006, 04:25 PM
  7. Number rows in a dynamic range
    By fishmen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-05-2006, 02:30 PM

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