Trying to reference two sheets but having trouble with second reference


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?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!