• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Chart Range

D

Dokat

Member
Hi,

I have below formula that gives me ranges between first cell with numeric value and last cell with numeric value. Range changes based on slier selection. When I enter below formula "Select data - Add- Series Values" to a chart I get "There is an error in formula you entered error message. Is there a work around this where Excel Graph reads blow formula as range? Thanks


="Sheet1!"&"H"&MATCH(INDEX(G2:G12,MATCH(TRUE,INDEX(ISNUMBER(G2:G12),0),0)),G:G,0)&":"&"H"&MATCH(LOOKUP(2,1/(G:G<>""),G:G),G3:G12,0)
returns Sheet1!H2:H9
 
Did you try adding the formula to the Name Manager then reference that Name in the Chart? You wouldn't need the "Sheet1!" piece though. If you have a sample workbook you can add that would make for better responses.
 
I tweaked the original formula little bit and it did the trick. Sharing incase if anyone come across similar issue

=INDIRECT("Sheet1!"&"H"&MATCH(INDEX(Sheet1!$H$1:$H$20,MATCH(TRUE,INDEX(ISNUMBER(Sheet1!$H$1:$H$20),0),0)),Sheet1!$H$1:$H$20,0)&":"&"H"&MATCH(LOOKUP(2,1/(Sheet1!$H$1:$H$20<>""),Sheet1!$H$1:$H$20),Sheet1!$H$1:$H$20,0))
 
You must log in or register to reply here.
Back
Top