Search for a Number from a Master Sheet across Multiple Sheets and Return Sheet Name

KathrynLindsay

I have a Master Sheet tracking new enquiries by a unique number, once they are logged here they move between several sheets moving through our divisions until they are finalised.

I would like to be able to have a "tracking" column that shows where in the cycle the enquiry number is up to by searching all of the sheets and returning the sheet name it is currently on.

Is this possible?

Andrée Starå

Hi @KathrynLindsay

Yes, it’s possible, but you’ll need to add a so-called helper column to the different sheets with the Sheet Name.

How many sheets do you want to include in the search?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

KathrynLindsay

Hi @Andrée Starå ,

Thank you for your response.

I thought as much, I know the Smartsheet Reports pull the sheet name but I didn't think you could do it with Formulas.

I have 7 sheets to search across, and have created a "helper" column on each sheet with the sheet name included.

So if "19-271" appears on any of the following sheets;

02 Consulting

03 Tendering

04 Submitted

05 Cold

06 PreConstruction

07 Won/Lost

In the master sheet (01 Enquiries) I want it to display the sheet name as its found, what is the best way to do this?

Thanks,

Kathryn

Andrée Starå

@KathrynLindsay

Happy to help!

You would either use a VLOOKUP or a combination of INDEX/MATCH for each sheet, and then the formula would look at the sheets one by one, and if there is a match, it will show the sheet name.

Make sense?

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)