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))

 

[leopardhawk]

@Fluff I changed the RRIF_Factors[Minimum (percentage) column in my Power Query to 'Whole Number'and then went back to your formula in post #11 and everything is working now. Of course, I had to swap the two ranges around as you alluded to in post #14. Anyway, all is good, thanks so much for your help!!!

 

[Dave Patton]

Your commentary and expected results may not be consistent with CRA rules.

 

[leopardhawk]

@Dave Patton with all due respect, I'm not sure what you mean. What commentary and what results?

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Dave Patton]

Your commentary is post #1 including "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."

1. Can you show why 0.0682 is correct if the person is 80 today. How is that number used?
2. Did you factor in the optional rate reduction for 2020.

Many formulas can yield a number. If one wants to use the a correct number or factor, the calculation would follow the defined rules.

I will assume that somewhere in your exercise you determine the correct factors and adjust to your actual requirements.

 

[leopardhawk]

@Dave Patton

1. When an individual in Canada owns a RRIF and the individual is 80 years old, they are required by law to withdraw an amount equal to 0.0682 percent of their RRIF the following year.

Your own post #13 has the exact same amount... see Named aRRIF.

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}

2. No. There is no law requiring me to do that.

Your commentary and expected results may not be consistent with CRA rules.

3. Your comments about 'following defined rules' and 'may not be consistent with CRA rules' are both unfounded, uncalled for, and rude. You know nothing about me or my workbook or the use that it is intended for, nor do I feel the necessity to explain it to you. It feels like you have singled me out for this attack and for reasons that remain a mystery. Perhaps it's because I didn't immediately jump on your suggestion and implement it within my workbook?