Error Message "Some cross-sheet formula can’t be updated"

Options

I read that in order to fix this error I need to replace my Vlookups with index/match formulas.

How does this decrease the number of references?

I have checked all my ranges below, but I cannot find the error. Any thoughts?

I need a date (located in first touch) returned from one of 10 workbooks, based on either the RA or Tracking#.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Workstation 1 First Touch},MATCH([RA #]@row,{Workstation 1 RA#}, 0)),INDEX({Workstation 2 First Touch},MATCH([RA #]@row, {Workstation 2 RA#}, 0))),INDEX({Workstation 3 First Touch},MATCH([RA #]@row,{Workstation 3 RA#}, 0))),INDEX({Workstation 4 First Touch},MATCH([RA #]@row,{Workstation 4 RA#}, 0))),INDEX({Workstation 5 First Touch},MATCH([RA #]@row, {Workstation 5 RA#}, 0))),INDEX({Workstation 6 First Touch},MATCH([RA #]@row, {Workstation 6 RA#}, 0))),INDEX({Workstation 7 First Touch},MATCH([RA #]@row,{Workstation 7 RA#}, 0))),INDEX({Workstation 8 First Touch},MATCH([RA #]@row,{Workstation 8 RA#}, 0))),INDEX({Workstation 9 First Touch},MATCH([RA #]@row, {Workstation 9 RA#}, 0))),INDEX({Workstation 10 First Touch},MATCH([RA #]@row,{WorkStation 10 RA#}, 0))),INDEX({Workstation 1 First Touch},MATCH([Tracking #]@row,{WorkStation 1 Tracking#}, 0))),
INDEX({Workstation 2 First Touch},MATCH([Tracking #]@row,{WorkStation 2 Tracking#}, 0))),INDEX({Workstation 3 First Touch}, MATCH([Tracking #]@row, {WorkStation 3 Tracking#}, 0))),INDEX({Workstation 4 First Touch},MATCH([Tracking #]@row,{WorkStation 4 Tracking#}, 0))),INDEX({Workstation 5 First Touch}, MATCH([Tracking #]@row,{WorkStation 5 Tracking#}, 0))),INDEX({Workstation 6 First Touch},MATCH([Tracking #]@row, {WorkStation 6 Tracking#}, 0))),INDEX({Workstation 7 First Touch},MATCH([Tracking #]@row,{WorkStation 7 Tracking#}, 0))),INDEX({Workstation 8 First Touch},MATCH([Tracking #]@row, {WorkStation 8 Tracking#}, 0))),INDEX({Workstation 9 First Touch},MATCH([Tracking #]@row,{WorkStation 9 Tracking#}, 0))),INDEX({Workstation 10 First Touch}, MATCH([Tracking #]@row,{WorkStation 10 Tracking#}, 0))),"")

Thank you for your help!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Because you are potentially referencing less cells.

    Lets say you are matching on Column 1 and pulling from Column 26. With a VLOOKUP, you have to reference every single column in between. That's a lot of cells being referenced that you just don't need. With INDEX/MATCH, you are only referencing Column 1 and Column 26. Of course smaller reference tables will have less columns in between which is why I say things like "could" and "potentially". It is also much more flexible to use an INDEX/MATCH. If you rearrange the columns in a table being used by VLOOKUP, you could break it. Since INDEX/MATCH uses single column references, you can move them around as much as you want to include having the matching value on the right side of the sheet.

Answers