Trying to reference two sheets but having trouble with second reference

Colleen Jones

Hello, I created active project and archive project sheets. When a project is completed, it is automatically moved to the archive sheet.

I set up a timesheet that references both sheets and looks for the project ID to automatically fill in the three fields for project name, project type, and solution type. It works fine for the active sheet.

I copied my formula from a different workspace and am having a little trouble getting the right reference (highlighted in yellow) from the archive sheet. I went to the reference, highlighted the Project Name column and named the reference accordingly, but I either end up with #No match or #Invalid.

=IFERROR(INDEX({AzS CE Active Projects Project Name}, MATCH([Project ID]@row, {AzS CE Active Projects Project ID Source}, 0)), INDEX({AzS CE Projects ARCHIVE Project Name}, MATCH([Project ID]@row, {AzS CE Projects ARCHIVE Source}, 0)))

Can anyone help me figure out what I'm doing wrong with that second reference?

Paul Newcome

Instead of editing the reference, you want to remove it entirely and create a new reference to the second sheet.

Paul Newcome

Instead of editing the reference, you want to remove it entirely and create a new reference to the second sheet.

Colleen Jones

Hi Paul, I haven't been able to figure out how to remove a reference, but I'll see if I can find the instructions for that. Thanks for your help!

Update: I found the instructions for the Reference manager and was able to fix the reference that was throwing an error. Thanks again! Colleen