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
Bookmarks