Use last value in a colum in a chart series

Right idea

Create two Dynamic Named Ranges and chart them rather than static ranges.

dnrA:

Formula:

Please Login or Register  to view this content.

dnrB:

Formula:

Please Login or Register  to view this content.

Is this resolved now? Did the updated sample file help to explain how it was done?



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

You're welcome. Thanks for the rep.


This explains a few ways to create a Dynamic Named Range: https://www.ablebits.com/office-addi...c-named-range/

Would recommend using the INDEX method (as I have) because OFFSET is a volatile function and can affect performance. Personally, I think using INDEX is simpler anyway.

Note that in this case you can't use COUNTA to determine the last row because there are gaps in column B. I use MATCH to find the last numeric value in column B.

Also note that whichever column you use to find the last row, use it in all the DNRs. See my example for a comparison.

If you want more web sites to review the methods, just Google: excel create dynamic named range or better: excel create dynamic named range using index match

This site explains the INDEX method well: https://exceljet.net/formulas/dynami...nge-with-index

More importantly, it has links that explain the different ways of determining the last row of data.

You're welcome.