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]

How about

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

 

[leopardhawk]

@Fluff thanks Fluff. As always, I appreciate your solutions, they always seem to work for me. One thing that has cropped up that was not mentioned in my original post and that is from age 95 and up, the percentage (Column D) remains constant at 0.2 (20%). What I have in those cells now is just a simple (=the cell above) and so on, down the column. Your formula works fine up to age 95 and then afterward, I am getting the dreaded #N/A error. Any ideas?

 

[Fluff]

As long as the RRIF_Factors[Age (at start of year)] column is sorted low to high, try changing the final 0 to a 1

 

[leopardhawk]

@Fluff we're getting closer. That works up to age 99 and then from age 100 onward, I'm still getting the #N/A error. Weird!

 

[Fluff]

Are the ages in the RRIF_Factors table numbers or text?

 

[Dave Patton]

consider the following. see CRA rates and rules.
Edit the cell references and the range of rates per your spreadsheet.

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

Cell Formulas
Range	Formula
C2:C3	C2	=DATEDIF(A2,DATE(B2,1,1),"Y")
D2:D3	D2	=LOOKUP(C2,$M$2:$N$47)

 

[leopardhawk]

@Fluff to answer your question, the 'age' column in the RRIF_Factors table is formatted as text, not sure why, but it's part of a Power Query and I'm not sure about changing it without causing problems elsewhere. The Power Query only goes as high as 95 years of age, making me wonder how it is that your formula works up to 99 years of age and then gives the error from age 100 onwards??

 

[Fluff]

As your ages are text 100 is less than 90
Try

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

 

[leopardhawk]

@Fluff the new formula is giving the #N/A error right off the bat... I tried changing the final 0 to a 1 and that made no difference either...hmmmmm