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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(RRIF_Factors[Minimum (percentage)],MATCH(C14&"",RRIF_Factors[Age (at start of year)],0))``

#### leopardhawk

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

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

##### Well-known Member

@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

##### MrExcel MVP, Moderator
Are the ages in the RRIF_Factors table numbers or text?

#### Dave Patton

##### Well-known Member

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

T202012c.xlsm
ABCD
1DOBYearAge Jan 1RRIF %
25-May-392020806.82%
35-May-14202010520.00%
1d
Cell Formulas
RangeFormula
C2:C3C2=DATEDIF(A2,DATE(B2,1,1),"Y")
D2:D3D2=LOOKUP(C2,\$M\$2:\$N\$47)

#### leopardhawk

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

##### MrExcel MVP, Moderator
As your ages are text 100 is less than 90
Try
Excel Formula:
``=INDEX(RRIF_Factors[Minimum (percentage)],MATCH(C14,RRIF_Factors[Age (at start of year)]+0,0))``

#### leopardhawk

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

Replies
8
Views
281
Replies
2
Views
407
Replies
4
Views
124
Replies
9
Views
759
Replies
1
Views
339

1,127,630
Messages
5,625,981
Members
416,148
Latest member
artcank

### 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.

### Which adblocker are you using?

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