Looking to reference a sheet name on a different sheet

Bill Raymond

I have a sheet that uses a nested formula to scan 3 different sheets to pull in specific data related to a Sales Order number.

Is there any way to craft a formula that will produce the name of the sheet where the information was pulled from?

-Using a report will not work because the function of the file is to pull in multiple data points when a Sales Order number is entered.

-It is impossible to add another column to the 3 sheets being scanned (columns maxed out)

-The 3 sheets being scanned by the formula are too big to be a single sheet (too many rows).

-1 of the 3 sheets is actively being updated.

-By next year, I will need to expand the formulas to include a 4th sheet that will have to be scanned.

-A helper sheet with a curated set of data (shrinking the number of lines to scan in to a single sheet) will not work because we cannot leave any of the data out.

Paul Newcome

You would need another nested formula (IF) to use the same logic in the same order but outputting the sheet name.

What is your current formula that pulls in the data?

Bill Raymond

=IFERROR((INDEX({iW Data Sheet - 2023 PO#}, MATCH([Sales Order #]@row, {iW Data Sheet - 2023 Sales Order #}, 0))), (IFERROR((INDEX({iW Data Sheet - 2021-2022 PO#}, MATCH([Sales Order #]@row, {iW Data Sheet - 2021-2022 Sales Order #}, 0))), (IFERROR((INDEX({iW Data Sheet - 2015-2020 PO#}, MATCH([Sales Order #]@row, {iW Data Sheet - 2015-2020 Sales Order #}, 0))), "ERROR")))))

This formula pulls the Purchase Order number (PO#) for the corresponding Sales Order number.

How could I adjust this to pull in which of the 3 sheets the information came from?

*The form is set up to pull different data points from the same destination (both sheet and row), so I am looking to create a column that just shows which sheet all of the data for each Sales Order came from, since all data in the same row will have come from the same sheet.

Paul Newcome

You would use an IF statement similar to this:

=IF(IFERROR(index_match_sheet_1, "") <> "", "Sheet 1 Name", IF(IFERROR(index_match_sheet_2, "") <> "", "Sheet 2 Name", ................................

Bill Raymond

Using IFERROR(index_match_sheet_1, "")<>"",.....

didn't work so after about 2 hours of trying to figure out the incomprehensible error messages I figured out that it should read IFERROR(index_match_sheet_1, "[literally anything]")<>"[the same thing]",....

because, for whatever reason, Smartsheet couldn't figure out how to deal with the blank spaces.

Paul Newcome

That's odd that it wouldn't work with the blanks. I use that pretty regularly. Glad you were able to get it working though.