leopardhawk

Well-known Member
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))

Fluff

MrExcel MVP, Moderator
Oops, yes the final 0 should have been a 1
Does this work
Excel Formula:
``=LOOKUP(C14,RRIF_Factors[Minimum (percentage)],RRIF_Factors[Age (at start of year)])``

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

leopardhawk

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

Dave Patton

Well-known Member
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}

Fluff

MrExcel MVP, Moderator
@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

Well-known Member

@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

Well-known Member
@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

MrExcel MVP, Moderator

The index match certainly works for me,
Using this
Excel Formula:
``=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

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

Fluff

MrExcel MVP, Moderator
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

Well-known Member
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!!

Replies
8
Views
279
Replies
2
Views
407
Replies
4
Views
123
Replies
9
Views
759
Replies
1
Views
320

1,127,481
Messages
5,625,015
Members
416,065
Latest member
meiravmeron

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.

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

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