Need some assistance with Excel? Let me know what you're working on!
Need some assistance with Excel? Let me know what you're working on!
I need to create a chart with a flexible scale. The X-axis values come from A20:AXX, with XX representing H9. The Y-axis data is in G20:GXX, also using H9 as the upper limit. This approach ensures only the desired data appears. I’ve explored the INDIRECT function and named ranges but haven’t succeeded yet.
A possible workaround is to duplicate the data and apply an IF statement to reset all values after H9 to zero, keeping the points visible on the graph.
Yes, I'm already handling that. The issue is the number of values ranges from 1000 to 100,000. In the worst case I'll have 99,000 empty points. That's why I'm choosing a dynamic approach—H9 can change between 1000 and 100,000. So if H9 equals 1000, it should plot A20 to A1000, and if H9 equals 100,000, it should plot A20 to A100,000.
This requires extremely careful handling of the information. It must be preserved accurately, ensuring no missing entries are omitted in the analysis. The data should be presented clearly, with each word from A20 to H9 displayed individually.
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
Thanks, I understood it involves naming and indirect approaches. I'll test it out tomorrow. I'll need to do a lot of cross-referencing in my work since the actual data points are scattered.
I adjusted the cells slightly and entered ="=INPUT!Q1:Q"&AB7 into an empty cell (AD10). The result now reads =INPUT!Q1:Q571, which matches what I need for the graph. I also created a name called percentage with the formula =INDIRECT(INPUT$AD$10), linking it to the sheet that holds the data and the cell containing the name. However, using that name in the graph still causes errors.
This functionality operates effectively... a sample .xlsx available here https://dl.dropboxusercontent.com/u/3247...TEST2.xlsx