Looking to reference a sheet name on a different sheet

Bill Raymond
Bill Raymond ✭✭
edited 10/12/23 in Smartsheet Basics

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.

Answers

  • Paul Newcome
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • =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
    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", ................................

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com