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

Ibrahim Khaleel

Dear Smart Team,

I am getting below error message again which cause not triggering workflow automatically.

"Some cross-sheet formula can’t be updated, because this sheet has more cell referenced from other sheets than the maximum allowed (100000)"

How can I get the permanent solution to avoid this error.

Refer attached images about Reference Managers and Error Message

Thanks for prompt support and appreciate in advance.

Best regards.

Paul Newcome

You are going to need to cut down on how many cells you are referencing. Maybe revisit some formulas to see if any can be made more efficient (INDEX/MATCH instead of VLOOKUP), see if there is some way to consolidate data on the source sheet(s) before referencing it.

Ibrahim Khaleel

Dear Paul,

Finally I replaced all vlookup formula with Index/Match formula since i have mapped data with multiple sheets and now issue is permanently resolved.

Thanks for your prompt support.

Best regards,

Paul Newcome

Happy to help. 👍️

Ibrahim Khaleel

Refer Attached Image

Ibrahim Khaleel

Refer the References Managers list

Paul Newcome

You are going to need to cut down on how many cells you are referencing. Maybe revisit some formulas to see if any can be made more efficient (INDEX/MATCH instead of VLOOKUP), see if there is some way to consolidate data on the source sheet(s) before referencing it.

Ibrahim Khaleel

Dear Paul,

Finally I replaced all vlookup formula with Index/Match formula since i have mapped data with multiple sheets and now issue is permanently resolved.

Thanks for your prompt support.

Best regards,

Ibrahim Khaleel

Paul Newcome

Happy to help. 👍️

jgrif17

Why does changing a vlookup to an index/match decrease the number of references?

Can you tell me what is wrong with this formula?

=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))),"")

Paul Newcome

@jgrif17 It doesn't decrease the number of references. It actually increases the number of references. What it CAN (but not always depending on your exact structure) decrease the number of cells referenced. Both of those things have their own separate limitations.

Are you getting an error message or an unexpected output with the above formula?