Is it possible to link cells in adjacent columns?

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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!

Cell Formulas
RangeFormula
B14B14=IF(personal_info!$E$9="","",MAX(YEAR(TODAY()),YEAR(personal_info!$E$9)+70))
C14C14=IF(personal_info!$E$9="","",MAX(70,YEAR(TODAY())-YEAR(personal_info!$E$9)))
D14D14=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("70",RRIF_Factors[Age (at start of year)],0))
B15:C25B15=IFERROR(B14+1,"")
D15D15=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("71",RRIF_Factors[Age (at start of year)],0))
D16D16=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("72",RRIF_Factors[Age (at start of year)],0))
D17D17=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("73",RRIF_Factors[Age (at start of year)],0))
D18D18=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("74",RRIF_Factors[Age (at start of year)],0))
D19D19=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("75",RRIF_Factors[Age (at start of year)],0))
D20D20=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("76",RRIF_Factors[Age (at start of year)],0))
D21D21=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("77",RRIF_Factors[Age (at start of year)],0))
D22D22=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("78",RRIF_Factors[Age (at start of year)],0))
D23D23=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("79",RRIF_Factors[Age (at start of year)],0))
D24D24=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("80",RRIF_Factors[Age (at start of year)],0))
D25D25=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("81",RRIF_Factors[Age (at start of year)],0))
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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!!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your commentary and expected results may not be consistent with CRA rules.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Dave Patton with all due respect, I'm not sure what you mean. What commentary and what results?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,000
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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
ABCDE
1DOBYearAge Jan 1RRIF %
25-May-392020806.82%6.82%
35-May-14202010520.00%20.00%
1d
Cell Formulas
RangeFormula
C2:C3C2=DATEDIF(A2,DATE(B2,1,1),"Y")
D2:D3D2=IF(AND(A2<>"",A2<DATE(YEAR(TODAY())-65,1,1)),LOOKUP(C2,$M$2:$N$47),"")
E2:E3E2=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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,823
Members
417,239
Latest member
AymericA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top