Forum Discussion

Matt McCormack's avatar
Matt McCormack
Copper Contributor
Apr 18, 2018

Referencing Entire Workbook

I am working to have a cell that includes all of the sheet names in a workbook that a certain value is present. For example, value=8,456; tabs where value is present B3 and C1; cell reads: B3, C1 My ...
Formulas & Functions
tips and tricks
Matt McCormack's avatar
Matt McCormack
Copper Contributor
Apr 18, 2018

 

SergeiBaklan wrote:

As a variant instead of using

=MID(CELL("filename"),FIND("]",CELL("filename",D5))+1,256)

you may extract sheet name from

=FORMULATEXT(D5)

 

That is a good idea, however, often the cells are set as values and no longer include the formulas. Any other ideas?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 18, 2018
=MID(CELL("filename",'T1'!B1),FIND("]",CELL("filename",'T1'!B1))+1,256)

if instead of D5 in formula you click on any cell in T1. For both CELL()

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 18, 2018

    Or you would like to find in which sheet you have combination of "Travel" and 2900?

    • Matt McCormack's avatar
      Matt McCormack
      Copper Contributor
      Apr 18, 2018
      SergeiBaklan wrote:

      Or you would like to find in which sheet you have combination of "Travel" and 2900?

      No I would just like the cell to return the tabnames in which the number is present.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 18, 2018

        Matt, as variant if you are on Excel 2016 with Office365 subscription you may

        - create helper table (or range) with all sheet names at any place of the workbook

        - combine string with sheet names where the value exists with TEXTJOIN

        =TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT(Sheets[Sheets]&"!1:1048576"),$D5)>0,Sheets[Sheets],""))

        above is array formula (Ctrl+Shift+Enter). And it's better to take more reasonable range for the sheets.