Invalid Reference

rarmstrong

I am trying to pull data from two sources to populate a new sheet. The source sheet is broken into two sheets (A-G and H-Z). The rows that need to reference the A-G sheet are returning fine, but the rows that need to reference the H-Z sheet are coming back with an Invalid Reference.

This is the formula:
=IF(AND(ISTEXT([FI Name]@row), LEFT(UPPER([FI Name]@row), 1) >= "H"), INDEX({iiCS H-Z CONTACT}, MATCH(FIID@row, {iiCS H-Z FIID}, 0)), IF(AND(ISTEXT([FI Name]@row), LEFT(UPPER([FI Name]@row), 1) <= "G"), INDEX({iiCS A-G CONTACT}, MATCH(FIID@row, {iiCS A-G FIID}, 0)), ""))

I have a similar formula for another column and this one (below) is working whether we need to reference the A-G sheet or the H-Z sheet

=IF(AND(ISTEXT([FI Name]@row), LEFT(UPPER([FI Name]@row), 1) >= "H"), INDEX({iiCS H-Z TRISM Family}, MATCH(FIID@row, {iiCS H-Z FIID}, 0)), IF(AND(ISTEXT([FI Name]@row), LEFT(UPPER([FI Name]@row), 1) <= "G"), INDEX({iiCS A-G Trism Familly}, MATCH(FIID@row, {iiCS A-G FIID}, 0)), ""))

Kelly Moore

Hey @rarmstrong

To fix a broken reference you must reinsert it. You will, one reference at a time, delete the reference from your formula and, using the Reference Another Sheet link in the formula window, reinsert the formula. Looking at your two formulas, my guess is that the culprit is your {{iiCS H-Z CONTACT} reference since the other H-Z range appeared in the 2nd formula.

Let me know if you need any help with the above.

Kelly