Can a formula automatically adjust at the end of each year?

[leopardhawk]

Cell D100 on my sheet has a simple formula pulling data from G11.

=G11

I need help with modifying the formula so that on December 31st at midnight the formula will automatically pull data from the next cell G12 and then the following year from G13 and so on each year into perpetuity...

Of course, only if this is even possible.

Thanks!

 

[Snakehips]

leopardhawk,

Perhaps something like....

=OFFSET(G11,YEAR(NOW())-2019,0)

Where 2019 is the year at the time you establish / require the return from G11

or maybe replace 2019 with an absolute cell reference containing the base year.

Hope that helps.

 

[leopardhawk]

Snakehips, thank you! I likely need to give you more info and possibly even a screenshot or two. Am I allowed to use DuckCapture to post screenshots?

 

[Snakehips]

I'm afraid I have no idea at all re the use of DuckCapture.
You can check out the links in my signature.

 

[leopardhawk]

In the screenshot below from the 'income analysis' tab, you can see cell D100 and below that, you can see the formula used to derive the data.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F
100	RRIF Monthly Withdrawals		$ 329.11	Annual Withdrawals	$ 3,949.35
101	Combined Total Income			Monthly	$ 329.11
102				Yearly	$ 3,949.35
income analysis

Cell Formulas
Range	Formula
D100	=(rrif!G11)

In the screenshot below from the 'rrif' tab, you can see cell G11 and the corresponding cell B11 which contains the year. So, what I need is for the formula in D100 on the 'income analysis' tab to pull data from column G for the actual year that it is at that time, even if it is thirty years in the future.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
8	RRIF Withdrawals
9	Estimated Value of RRSP at end of YYYY				$ 78,987.00
10	Year	Age *	Percentage **	Amount	RRIF Value ***	Monthly Withdrawals
11	2018	70	0.05	$ 3,949.35	$ 79,184.47	$ 329.11
12	2019	71	0.0528	$ 4,180.94	$ 79,160.71	$ 348.41
13	2020	72	0.054	$ 4,274.68	$ 79,041.97	$ 356.22
14	2021	73	0.0553	$ 4,371.02	$ 78,820.65	$ 364.25
rrif

 

[leopardhawk]

not sure why it did all that, it looked fine in the preview...?!?

 

[Snakehips]

Then assuming that the years in column B are in number format and that there are sufficient not to cause an error then...

=OFFSET(rrif!G11,YEAR(NOW())-rrif!B11,0)

 

[leopardhawk]

Tony, fantastic! That appears to do the trick... I will run some more tests by changing dates in B11 to ensure that there are no errors. Thanks so much for your help!

 

[leopardhawk]

Tony, one more related question.

Where in the formula would I put a /2 if I want to cut the amount in half that is being imported from 'rrif' G11...?

Thanks!

 

[Snakehips]

=OFFSET(rrif!G11,YEAR(NOW())-rrif!B11,0)/2