'How do I reference a cell in range of worksheets and look for a specific value in Excel?
Is this possible?
=IF(COUNTIF('**ALLSHEETS**'!D4, "ICT"),**SHEETNAME**, FALSE)
Excel will look up a a specific value ("ICT") in the same cell in every sheet then if that cell contains the value it will return the name of the sheet(s)
Solution 1:[1]
You can do it with a little trick
Put this Formula in Cell A1 of Everysheet you want to get searched
=IF(D4="ICT",MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255),"")
Then, in the required sheet!cell add all sheets like
=Sheet1!A1&Sheet2!A1&Sheet3!A1
Note: If it would be only one sheet having ICT and you are trying to find the sheet name, then this would be the easiest solution But in case of multiple sheets having ICT in D4, the result would be a like
sheet1sheet2sheet3 in the same cell.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Mian |