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

jgrif17

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!

Paul Newcome

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.

Paul Newcome

Are you getting an error message or an unexpected output?

jgrif17

I am getting a syntax error.

Paul Newcome

Which error exactly?

jgrif17

It just said, your syntax isn't quite right… I fixed it, it had to do with the IFERROR loop and all the parenthesis.

But can you tell me, Why does a match/index fix the reference limit issue?

This is a really big build made more complicated by the line limit, I need to understand how this fixes the issue and to what limit so I can finish my rebuild with confidence.

Paul Newcome

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.

jgrif17

Thank you.

Paul Newcome

Happy to help. 👍️