need some excel help again.

[looney]

I need to make a graph with a dynamic range.

The X-axis data is in A20:AXX where XX is the value in H9.

Same goes for the Y-asix data (G20:GXX) where again H9 defines the end of the range.

This way it will only show the data that I want it to show.

I have looked into the INDIRECT function and the named ranges but I cant get it to work

[MrSuperb]

I need to make a graph with a dynamic range.

one (workaround) solution would be to copy the data and use IF to set all those values after H9 to x=0,y=0.

The points would still show up in the graph though

[looney]

one (workaround) solution would be to copy the data and use IF to set all those values after H9 to x=0,y=0.

The points would still show up in the graph though

Yes, I'm already doing that, the problem is that the amount of values can vary from 1000 to 100,000.

So in a worst case scenario i will have 99,000 empty points hence is why I'm going for the dynamic approach

H9 is the value that can vary from 1000 to 100,000

So if H9=1000 then it should graph A20:A1000 and if H9=100000 it should graph A20:A100000.

[MrSuperb]

another (more elegant) solution would be to just filter data, but then you still won't be able to use your 'H9'

(there is a checkbox in the diagram options to not draw hidden data)

[looney]

another (more elegant) solution would be to just filter data, but then you still won't be able to use your 'H9'

(there is a checkbox in the diagram options to not draw hidden data)

It is very delicate / important data and it does not allow me to do this as there can also be valid empty data that should never be left out in this report.

it needs to graph A20:A(H9) word by word

[MrSuperb]

It is very delicate / important data and it does not allow me to do this as there can also be valid empty data that should never be left out in this report.

it needs to graph A20:A(H9) word by word

alright. it works with INDIRECT as well

1. somewhere in your sheet write ="=Sheet1!A20:A"&H9 .... e.g. in cell A1

2. define new name under Insert > name > define

3. choose any name like 'series' and enter =INDIRECT(Sheet1!$A$1)

4. in the graph enter =yourworkbook.xlsx!series

[looney]

Thanks, I knew it was something with naming and indirect, will try it out tomorrow, I need to do quite a bit of cros sheeting in my case as the actual cells are all over the place

[looney]

I had to change the cells a bit but i now typed in ="=INPUT!Q1:Q"&AB7 in a empty cell (AD10).

The cell now shows =INPUT!Q1:Q571 which is correct as I want it to graph up to Q571.

I have also made a name, i named it percentage and it contains the following code: =INDIRECT(INPUT!$AD$10) where INPUT is the sheet that contains the data and the cell of the name.

Now I need to use that name in the graph but that keeps giving me errors..

[MrSuperb]

Now I need to use that name in the graph but that keeps giving me errors..

 

Did you use the workbook name before the specified name?

 

i.e. =yourworkbook.xlsx!percentage ( =yourworkbook!percentage might also work)

[MrSuperb]

It definetly works ...

 

 

here a sample .xlsx

https://dl.dropboxusercontent.com/u/3247020/share/TEST2.xlsx

[looney]

yes i made sure to use the workbook name but did not work, will look at your file and try again later.

Thanks for the help so far