Forum Discussion
Chart linking with Name Manager
so it's known chart behave different than other items, i linked it to a box that indirect the limit of which it should take values from a column,
like i used this code to do it: =OFFSET('Study '!$F$25,1,0,'Study '!$BD$26), but this fails when i want it to take starting and ending limit with offset command, it just re input values, like if i want beginning to be 100 and ending to be 300 it reads first 100 and then reread them plus the extra 200 so i have 400 values.
is it possible to make it so from column F it starts taking values from lets say box BC and the ending limit to be from BD? i tried looking it up and figuring it but you know how it goes with charts
2 Replies
HelloAbdulrahim-tu
You’re right that `OFFSET` works best when you have a starting cell and a length, but it doesn’t directly handle “start at row X and end at row Y.” That’s why you’re seeing duplication when you try to use beginning and ending limits.
For chart ranges where both the start and end are dynamic, `INDEX` is the better function. You can define the range like this:
=INDEX('Study'!$F:$F,'Study'!$BC$26):INDEX('Study'!$F:$F,'Study'!$BD$26)
• `INDEX('Study'!$F:$F,'Study'!$BC$26)` → returns the starting cell in column F, based on the row number in cell BC26.
• `INDEX('Study'!$F:$F,'Study'!$BD$26)` → returns the ending cell in column F, based on the row number in cell BD26.
• The colon (`:`) creates a proper range between those two cells.
So if BC26 = 100 and BD26 = 300, the formula resolves to:=F100:F300
This way, your chart series will only plot values between the start and end limits, without duplication.Steps to apply:
1. Go to Formulas > Name Manager.
2. Create a new name (e.g., `ChartRange`).
3. In the “Refers to” box, paste the formula above.
4. Use `ChartRange` as the series values in your chart.This approach avoids the pitfalls of `OFFSET` and gives you a clean dynamic range that respects both start and end limits.
It seems to work for me. I defined names:
xvalues: =OFFSET(Sheet1!$F$1,Sheet1!$C$26-1,0,Sheet1!$D$26)
yvalues: =OFFSET(Sheet1!$G$1,Sheet1!$C$26-1,0,Sheet1!$D$26)
