Forum Discussion
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 ...
Or you would like to find in which sheet you have combination of "Travel" and 2900?
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.
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.
- That is incredible. Quick question, do you know why it works until I enable editing?
Sorry, didn't catch, what do you mean under "it works", you may edit something in the protected view?