Cross Reference Sheet max limit

jeanniesry_4

Hello, is there a workaround to this error message. It seems there is a maximum to cross referencing sheet when using the INDEX MATCH formula. I have 2 sheets with over 50 columns and need to continue index/matching each one but I hit a dead end.

BullandKhmer

Pretty sure the max limit is 100 per sheet. If you're only referencing 50 columns then you have probably built a bunch of redundant references. to find out right click in any cell and select manage references.

jeanniesry_4

Hi @BullandKhmer -

I have a total of 141 columns in my sheet.

I checked the sheet reference manager and I see total range references is at 101. I deleted the INDEX MATCH formula from multiple columns already but still getting the info in screenshot below.

If I delete a reference from the manage ref window rather than the column itself, will that delete just what is in the column? Asking because last time I deleted a ref from one column and it deleted every INDEX MATCH crossed-ref from the entire sheet. I couldn't undo what I did and had to add the INDEX MATCH back into each column. I want to avoid this.

BullandKhmer

Um OK, so a couple of things:

If you delete the ref from the reference manager it will not be recognized in the sheet, resulting in an error message #INVALID REF. It wont delete any formulas or data from the sheet though.

But, If you need to reference 141 columns using index match, you are going to have a problem. As a work around you could change your INDEX MATCH formulas to VLOOKUPS, as this will only require one reference for multiple columns. The drawback is that if you move columns in the source sheet it will alter what the VLOOKUP references, so you will need to be aware of that.

Cheers.