Need help with a formula that pulls from two sheets but gives #INVALID REF for one sheet

Colleen Jones

I had help a year ago to create a formula that uses a Project ID to pull data from two project sheets into a sheet that tracks project hours. I need to pull another column of data into that sheet, but my formula is only half working. It pulls from the active projects sheet but I'm getting #INVALID REF for data from the archive sheet. The sheets were renamed when the team name changed, but that doesn't seem to be an issue with the other columns, so I'm not sure what's causing the issue with this addition one. Here is the formula:

=IFERROR(INDEX({SIDD Active Projects CE&T Functional Team}, MATCH([Project ID]@row, {SIDD Active Projects Project ID Source}, 0)), INDEX({SIDD ARCHIVE CE&T Functional Team}, MATCH([Project ID]@row, {SIDD ARCHIVE Source}, 0)))

I have to have this fixed in the next hour, which would take a miracle, but I'll keep trying different things and hope someone sees this right away. If not, I'll fix it after the fact as we need it going forward. Thanks! 😯

Paul Newcome

If you click inside of each {Cross Sheet Reference}, you will see a small blue link in the formula helper box that says either "Reference another sheet" or "Edit Reference". If any of them say the first, remove them completely from the formula and recreate them from scratch.

Colleen Jones

I can't figure out how to remove them from the formula. If I type the same name, then it just grabs the existing one. I must be missing something in this correction process. Thanks for your help, Paul!

Paul Newcome

Try highlighting the whole {cross sheet reference} including the curly brackets on either side and then hit the backspace button on your keyboard.

Colleen Jones

Okay, taking out the curly brackets with the reference and redoing it for the archive sheet fixed the issue! Thanks again for your help, Paul! This was driving me crazy for the last couple of days.

Paul Newcome

Happy to help. 👍️