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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(RRIF_Factors[Minimum (percentage)],MATCH(C14&"",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 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
Joined
Jun 12, 2014
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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
Joined
Jun 12, 2014
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
Are the ages in the RRIF_Factors table numbers or text?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Jun 12, 2014
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,380
Messages
5,641,796
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