Return all sheet names a value appears on

[Mydako]

I'm looking for a formula that lets me input a value, and the output would be a list of whatever sheets in the workbook that value appears on. I don't have the slightest idea in this case, or know if it's even possible. It can't be VBA because our work computers can't all handle it.

Longer context:

My company has thousands of items that we sell to hundreds of retailers. I have a master workbook that has a sheet for each retailer and a list of all the items they sell. Sometimes though an item is discontinued, or needs a price change, or whatever. I have to search the sheets, go to those retailers, change the information, and log it. While I can use Find All, I was wondering if I could make a formula where I could put the item ID# into a cell, and then beside it I would receive a list of sheets(retailers) that have that ID#. If I have something that outputs all the sheet names it will make it a lot easier for me to properly report and log the changes, otherwise it's a lot of back and forth between vlookups and Find All.

But for all I know this might not be possible, if it is, it's outside my usual box for thinking of these things.

 

[Yongle]

Here is one way
ID assumed to be in column A in each subsidiary sheet

Add a sheet (I named it "Qry")
List sheets from A2 downwards (see below if you cannot paste a list from somewhere and want to avoid all that typing)
Formula in B2 copied down:
=COUNTIF(INDIRECT("'"&A2&"'!A:A"),$B$1)
In the picture below, values in column B are the number of times ID0010 appears in colunn A in each sheet (named R1,R2,R3)

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Sheet
Name[/td][td=bgcolor:#FFFF00]ID0010[/td][td] Formula[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Qry[/td][td]

0​

[/td][td] =COUNTIF(INDIRECT("'"&A2&"'!A:A"),$B$1)[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]R1[/td][td]

6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]R2[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]R3[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Qry[/td][/tr][/table]

Automating list of sheets
The list of sheet names in column A can be automated like this:

Create Named Range with name "Sheets" and RefersTo
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Formula in A2 copied down until #REF ! appears
=INDEX(Sheets,ROWS($A$1:$A1))

NOTE - formula does not auto-update if sheet names are changed, sheets added or deleted
Select cell A2 \ {F2} followed by {ENTER} to force A2 to update \ drag down formula until #REF ! appears

 

[Yongle]

It can't be VBA because our work computers can't all handle it.

You may want to ignore Automating Sheet Names
- the technique illustrated uses a precursor to VBA
- I forgot that the workbook must then be saved as macro enabled even though everything is done by formula

 

[Mydako]

Thanks so much Yongle, that's perfect. Sure I'll have to make my own list cause of the VBA issues, but that's just something that needs to be done once. Thanks this will really make my life easier after the next round of discontinuations. I can just add a filter to sort out the ones that match.

 

[Yongle]

thanks for your feedback