SOLVED
Home

Dynamic range of data for graph

%3CLINGO-SUB%20id%3D%22lingo-sub-673796%22%20slang%3D%22en-US%22%3EDynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673796%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20many%20days%20of%20trials%20I%20must%20ask%20for%20help.%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20graph%20from%20range%20of%20data%2C%20which%20first%20cell%20is%20defined%20but%20the%20last%20cell%20changes%20based%20on%20value%20in%20another%20cell.%20All%20data%20is%20in%20one%20column%20so%20only%20row%20number%20changes.%20A%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20possible.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-673796%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-674762%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674762%22%20slang%3D%22en-US%22%3ETo%20return%20the%20ADDRESS%20of%20the%20last%20cell%2C%20use%20this%20formula%3A%3CBR%20%2F%3E%3DADDRESS(A3%2B2%2C3)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676985%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EThank%20you%20for%20your%20answer%2C%20but%20this%20is%20not%20the%20problem%20I%20have.%20Such%20formula%20I%20wrote%20already%20in%20cell%20F4.%3C%2FP%3E%3CP%3EThe%20problem%20is%20how%20to%20use%20it%20in%20graph.%20The%20range%20like%20%22%3DSheet1!%24C%243%3A%24C%2432%22%20is%20OK%20but%20%22%3CSPAN%3E%3DSheet1!%24C%243%3AADDRESS(A3%2B2%3B3)%22%20doesn't%20work.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677012%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355761%22%20target%3D%22_blank%22%3E%40Waceks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20entered%20%22%3DDynamicSeriesValues!SeriesValues%22%20in%20the%20Series%20Values%20box%20of%20the%20Edit%20Series%20tab.%20SeriesValues%20is%20a%20named%20range%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DArkusz1!%24C%243%3AINDEX(Arkusz1!%24C%3A%24C%2CArkusz1!%24A%243%2B2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPlease%20modify%20the%20value%20in%20A3%20to%20see%20the%20effect%20of%20the%20dynamic%20range%20on%20the%20chart.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677015%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EIt%20is%20exactly%20what%20I%20needed.%20Thank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677017%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20of%20data%20for%20graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677017%22%20slang%3D%22en-US%22%3EYou're%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Waceks
New Contributor

After many days of trials I must ask for help.

I need to create a graph from range of data, which first cell is defined but the last cell changes based on value in another cell. All data is in one column so only row number changes. A have attached an example.

I hope this is possible.

5 Replies
To return the ADDRESS of the last cell, use this formula:
=ADDRESS(A3+2,3)

@TwifooThank you for your answer, but this is not the problem I have. Such formula I wrote already in cell F4.

The problem is how to use it in graph. The range like "=Sheet1!$C$3:$C$32" is OK but "=Sheet1!$C$3:ADDRESS(A3+2;3)" doesn't work.

Solution

@Waceks 

In the attached file, I entered "=DynamicSeriesValues!SeriesValues" in the Series Values box of the Edit Series tab. SeriesValues is a named range with this formula: 

=Arkusz1!$C$3:INDEX(Arkusz1!$C:$C,Arkusz1!$A$3+2)

Please modify the value in A3 to see the effect of the dynamic range on the chart. 

@TwifooIt is exactly what I needed. Thank you very much.

You're very much welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies