I created an INDEX/MATCH function to retrieve data from a master Supply Risks worksheet in a master workbook (Supply Master Tracker) to populate into 20 columns into my tracking workbook (Personal Supply Tracker)
While my formula works, it does not seem to update when new rows and columns are added to the master workbook (which is a weekly occurrence), hence I have to update the formula to reflect the new column number.
This is quite tedious as I have to change the formula of 20 columns in many risks buy tracker workbook twice a week to match the new column numbers of the master worksheet. I was under the impression that Index Match functions allowed for new columns to be added.
Is there anything I can do to avoid having to alter the first range of my INDEX/MATCH function constantly? Maybe link it to the individual name of the column ie. (CE Comment) vs. (Column AK)?
Example: Personal Supply Tracker: Column N (Comments) cell N3 contained
=IFERROR(INDEX(‘[Supply Master Tracker.xlsx]FY21 Supply Risks’!$AH$2:$AH$263,MATCH($A3,'[Supply Master Tracker.xlsx]FY21 Supply Risks’!$C$2:$C$263,FALSE))&””,””)
However, the tracker updated so I had to change to:
=IFERROR(INDEX(‘[Supply Master Tracker.xlsx]FY21 Supply Risks’!$AK$2:$AK$263,MATCH($A3,'[Supply Master Tracker.xlsx]FY21 Supply Risks’!$C$2:$C$263,FALSE))&””,””)
Please advise of any suggestions.
