Looking to reference a sheet name on a different sheet
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.