Is it possible to link cells in adjacent columns?

[leopardhawk]

Hello forum friends, this is probably a long shot but I am hoping that there is a way, even if it involves some VBA code, to link adjacent cells within two columns so that they are always side-by-side but ONLY after the first column has a value in it and ONLY if necessary.

To (hopefully) explain... the two columns in question are AGE and Percentage* shown below. As you can see from the formulas in the AGE column, the column won't contain any values until the user enters a date in "personal_info!$E$9". As long as the user enters a Date of Birth making them less than 71 years of age the two columns (AGE and Percentage) the AGE column will always start with 70 (in C14) and the Percentage * column will always start with 0.05 (in D14) so they coincide with each other. The percentages shown in Column D are 'fixed' in that they each relate to a particular age, from 70 years old and up. i.e. the percentage for a person who is 80 years old should ALWAYS be 0.0682.

My issue arises when, if the user enters a date in "personal_info!$E$9" making them 80 years old today, the way it is now, the AGE column will show 80 in C14 but D14 will still show 0.05 and I would like D14 to then show 0.0682 which is the correct percentage for a person that old. Of course, all the other cells in Column D would also show the correct percentage relative to the AGE of the user. I hope this makes sense and that there is a solution. Let me know if you need any further information. Appreciate any and all suggestions. Thanks!

Book1
	B	C	D
13	Year	Age	Percentage *
14			0.05
15			0.0528
16			0.054
17			0.0553
18			0.0567
19			0.0582
20			0.0598
21			0.0617
22			0.0636
23			0.0658
24			0.0682
25			0.0708
rrif

Cell Formulas
Range	Formula
B14	B14	=IF(personal_info!$E$9="","",MAX(YEAR(TODAY()),YEAR(personal_info!$E$9)+70))
C14	C14	=IF(personal_info!$E$9="","",MAX(70,YEAR(TODAY())-YEAR(personal_info!$E$9)))
D14	D14	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("70",RRIF_Factors[Age (at start of year)],0))
B15:C25	B15	=IFERROR(B14+1,"")
D15	D15	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("71",RRIF_Factors[Age (at start of year)],0))
D16	D16	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("72",RRIF_Factors[Age (at start of year)],0))
D17	D17	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("73",RRIF_Factors[Age (at start of year)],0))
D18	D18	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("74",RRIF_Factors[Age (at start of year)],0))
D19	D19	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("75",RRIF_Factors[Age (at start of year)],0))
D20	D20	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("76",RRIF_Factors[Age (at start of year)],0))
D21	D21	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("77",RRIF_Factors[Age (at start of year)],0))
D22	D22	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("78",RRIF_Factors[Age (at start of year)],0))
D23	D23	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("79",RRIF_Factors[Age (at start of year)],0))
D24	D24	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("80",RRIF_Factors[Age (at start of year)],0))
D25	D25	=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("81",RRIF_Factors[Age (at start of year)],0))

 

[Fluff]

Oops, yes the final 0 should have been a 1
Does this work

=LOOKUP(C14,RRIF_Factors[Minimum (percentage)],RRIF_Factors[Age (at start of year)])

 

[leopardhawk]

@Fluff strangely enough, that returns the number 95 to the cell with the formula in it.

 

[Dave Patton]

Did you read post #7?
If the rates are for residents of Canada, the rates that I used are from CRA.
I extended the table to age 110. I took a second and put the rates on the sheet.
Why not use numbers instead of text?
You can convert the range F9 and name the information say aRRIF (see example).

T202012c.xlsm
	A	B	C	D	E
1	DOB	Year	Age Jan 1	RRIF %
2	5-May-39	2020	80	6.82%	6.82%
3	5-May-14	2020	105	20.00%	20.00%
1d

Cell Formulas
Range	Formula
C2:C3	C2	=DATEDIF(A2,DATE(B2,1,1),"Y")
D2:D3	D2	=IF(AND(A2<>"",A2<DATE(YEAR(TODAY())-65,1,1)),LOOKUP(C2,$M$2:$N$47),"")
E2:E3	E2	=LOOKUP(C2,aRRIF)

Named aRRIF ={65,0.04;66,0.0417;67,0.0435;68,0.0455;69,0.0476;70,0.05;71,0.0528;72,0.054;73,0.0553;74,0.0567;75,0.0582;76,0.0598;77,0.0617;78,0.0636;79,0.0658;80,0.0682;81,0.0708;82,0.0738;83,0.0771;84,0.0808;85,0.0851;86,0.0899;87,0.0955;88,0.1021;89,0.1099;90,0.1192;91,0.1306;92,0.1449;93,0.1634;94,0.1879;95,0.2;96,0.2;97,0.2;98,0.2;99,0.2;100,0.2;101,0.2;102,0.2;103,0.2;104,0.2;105,0.2;106,0.2;107,0.2;108,0.2;109,0.2;110,0.2}

 

[Fluff]

@Fluff strangely enough, that returns the number 95 to the cell with the formula in it.

Oops, you need to swap the two ranges round.

 

[leopardhawk]

@Fluff I tried that (switching the two ranges) around before I saw your last post and I'm still getting the #N/A error. It should work though, eh?

 

[leopardhawk]

@Dave Patton yes, Dave I saw your post. Thank you for stepping into this. I want to see if I can resolve it using Fluff's suggestions. Your method, while it may work, could also change things dramatically in my workbook. I'm also not sure if I even 100% understand what you are proposing. I'm confused by some of what you had in post #7.

If what I'm trying now doesn't work, I will try and do as you suggest and let you know the outcome. Thanks!

 

[Fluff]

The index match certainly works for me,
Using this

=INDEX(RRIF_Factors[Minimum (percentage)],MATCH(C14,RRIF_Factors[Age (at start of year)]+0,1))

in a cell select that cell & on the formula tab click "Evaluate formula" then click the evaluate button until it adds 0 to Ages, does it show something like
{80;81;82;83;84;85;86;87;88;89;90}

 

[leopardhawk]

@Fluff no, I get {80,"72"+0} and on next click, I get the #N/A error...

 

[Fluff]

I thought that 2016 could handle that, but obviously not. In that case I'm not sure what else to suggest, other than changing the PQ to return numbers & not text.

 

[leopardhawk]

I was thinking maybe to try that. Failing that, I guess I can end my worksheet at 99 years of age, pretty sure most folks won't live beyond that anyway, I just didn't want to put an upper limit on it what with all the advances in medical science! Thanks for all your efforts!!