Creating chart w/ formula based dynamic axes

**mmorri4** · 05-24-2010, 04:39 PM

I'm trying to create a chart that would illustrate the change in an individuals net worth over the course of their retirement, from retirement age to their death, with age on the x-axis and net worth on the y-axis.

In B2 i have the retirement age linked from another sheet. In B3 and down, I have the following formula: =IF(B2<Summary!$J$8,B2+1,""), =IF(B3<Summary!$J$8,B3+1,""), etc..., with Summary!J8 being the linked illustration age or the age of death.

In C2 and down, i have the net worth that corresponds to the age in that particular row:
e.g. =IF(B3="","",Summary!L46), =IF(B4="","",Summary!L47), etc. where summary!46 is the linked net worth from another sheet

In a typical situation, there will be 5-10 rows that go unused and are filled with "". I want to create a chart that will only show the cells currently in use and not filled with "" and automatically update the x and y-axes if there is a change in the illustration age. Say, for example, a client wants to see what their net worth at death would be if they were to die at 90 instead of 85, the column chart would automatically update the x-axis to go out to 90 instead of to 85 and fill in those corresponding net worth values.

Is this possible? I've spent all day fooling around with offset to no avail and was hoping somebody could point me in the right direction. If I haven't made something clear, just say so and I'll try and explain it better.

Thank You

**teylyn** · 05-24-2010, 05:59 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**mmorri4** · 05-25-2010, 09:49 AM

Sorry about that. Attached is a dummy template of what I'm talking about.

As you can see, there is a huge gap in the first chart from where the data ends to where the chart ends. As you increase the Illustration age in cell B5, the amount of data in the chart grows out to the right.

What I want is to have it be so there is no gap in the chart occupied by the "" 's from the unused cells. So, if the illustration age goes up or down, the chart updates itself to reflect the new data. They would ideally look like the second two charts assuming the middle one has an illustration age of 85 and the bottom one an illustration age of 90 where both are separate incarnations of the same chart.

I hope this makes sense. Thank You.