Table length by input

[Gfsrch]

Trying to have input determine length of the sheet. Ex: Someone is 40 years old currently and wishes to retire at age 70, result is excel uses input to create the sheet with 30 rows to match the 30yrs, and if someone is 35 and wishes to retire at 75 the table would have 40 rows. Would not think it is overly difficult, but definitely beyond my knowledge. Hopefully someone can "school" me. Thanks

[FDibbins]

Hi, welcome to the forum

Doing that should not really be a problem, but what do you ant shown for those years, and what does the rest of your sheet look like?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Gfsrch]

Thanks Ford,
I know this is not what you are asking, but either a little bleary eyed and new to forum, as well as rusty on excel. At any rate did not see the upload feature, and feeling incredibly addled at the moment.
Below is a bit disjointed, but the idea is instead of it being a static 30 yr, the table would adjust to the input of age and desire retirement age. The formulas across are constant with some pulling of data from a simple input form.
Please don't laugh too much at my asking questions when unable to simply upload the sheet, will do happily once I have a rectal crainiotomy performed.

Greg


Invested Total with Match Gain Taxed Annually
The CONTRIBUTION & ACCUMULATION PHASE Calculations
Year Age Value Investment Emp Match Growth Fees Year Est. Acct Value Total Fees
0 40 $500,000 $500,000 60% 0
1 41 $12,000 $7,200 1
2 42 $12,000 $7,200 2
3 43 $12,000 $7,200 3
4 44 $12,000 $7,200 4
5 45 $12,000 $7,200 5
6 46 $12,000 $7,200 6
7 47 $12,000 $7,200 7
8 48 $12,000 $7,200 8
9 49 $12,000 $7,200 9
10 50 $12,000 $7,200 10
11 51 $12,000 $7,200 11
12 52 $12,000 $7,200 12
13 53 $12,000 $7,200 13
14 54 $12,000 $7,200 14
15 55 $12,000 $7,200 15
16 56 $12,000 $7,200 16
17 57 $12,000 $7,200 17
18 58 $12,000 $7,200 18
19 59 $12,000 $7,200 19
20 60 $12,000 $7,200 20
21 61 $12,000 $7,200 21
22 62 $12,000 $7,200 22
23 63 $12,000 $7,200 23
24 64 $12,000 $7,200 24
25 65 $12,000 $7,200 25
26 66 $12,000 $7,200 26
27 67 $12,000 $7,200 27
28 68 $12,000 $7,200 28
29 69 $12,000 $7,200 29
30 70 $12,000 $7,200 30

[FDibbins]

I will see what I can do with your sample.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[FDibbins]

OK, I cant make out (without guessing) where 1 heading ends and the next starts?

[Gfsrch]

Seemed to attach, after uploading closed manage attachments window and reopened and it was there. But again GIGO.

[FDibbins]

Thanks for the file

So, where would you put the age, and where - and what - would you want the output to be?

[Gfsrch]

I have an input form. I copied just this page as the entire spreadsheet is 8-10 sheets at this time. If you look at C4 it indicates the source sheet I am pulling from for that cell. I manually made this sheet 30 rows deep but wish to automate so the length varies. Attached the input form and the actual client is 49 so hoped I could automate the Accumulation sheet to shorten to 21yrs, in this case, or as needed shorten or lengthen.

[Gfsrch]

Well, did not successfully attach input will try again.

[FDibbins]

OK so you have 2 files, 1 with the data tables, and another 1 to show the extract?
(why not use a 2nd sheet, instead of a 2nd file - not that it really matters, just makes formulas a bit simpler)

[Gfsrch]

On the complete spreadsheet it is all 1 file, separated as not to confuse the issue.

[FDibbins]

Ok can you upload a sample of the actual file, please?
(its getting late here, I will probably need to carry on in the morning )

[Gfsrch]

Not sure how that helps answer the question. Assume the entire file is the two I have uploaded. Do you know how to solve the problem? Sleep well.

[FDibbins]

See if this works for you, assuming Input is in the same file as Accum
No changes to Input
Accum sheet...
A5=IF(B5="","",A4+1)
B5=IFERROR(IF(B4+1>'Input form'!$B$19,"",B4+1),"")
C5=nothing
D5=IF(B5="","",'C:\Users\Owner\Desktop\VirtualInsPart\[Calculator sheet A.xlsx]Calculator Accum & Dist'!$B$5*12)
E5=IF(B5="","",D5*$E$4)
H5=IF(B5="","",H4+1)

all copied down as needed, say, row 40

[protonLeah]

Copy down to row 65 or so...

A6:

Please Login or Register  to view this content.

B6:

Please Login or Register  to view this content.

cell names:
CurrentAge =Sheet1!$B$1
RetirementAge =Sheet1!$D$1

try changing the values in b1 and d1...

[Gfsrch]

That is exactly the result desired. Please excuse my extreme ignorance. How do I then have the remaining cells follow suit. Ultimately hoping to have totals for the columns at bottom, if possible. I have attached the whole workbook. I started to think having a second page with only the Accumulation and then a function to copy the appropriate portion to first page. But again an idea from an excel ignoramus.

[Gfsrch]

On Input & Results B4-E25 is input section used to populate the mirror Accumulation table. Cont. & Accum Calc is where I thought possibly using the "years to retirement" to determine amount of table to copy to Input & Results at B34. But seems addled as would think this is something within Excel capabilities, just to clueless to know.

[Gfsrch]

I owe you an apology for not thanking you for your attempt to assist. Was going a bit guano meshuggah and bleary eyed.

[AliGW]

You have opened a thread in commercial services asking the same question. You cannot have both. Do you wish to keep this one open or switch to commercial services?

[Gfsrch]

Please close this thread as I wish to switch to commercial services.
Thank you

[Gfsrch]

Ford,
Moved this to commercial and saw you there. Coming to the understanding of how far out of my depth I am. Thanks for your assistance. Have I believe an easier way to approach, if able to help would like to work with you. New to this so don't know what the going rate in points is. Thanks again for all your help.
Greg

[FDibbins]

Happy to help, thanks for the feedback