EXCEL

Occasional Visitor

Hi

In Excel, how can I refer in a "COUNTIF" formula to a worksheet name where the worksheet name varies but is contained as a value in a cell on the originating worksheet?

EG. cell a1 contains a worksheet-name as a text value, cell a2 contains a text value, and cell A3 contains a formula whose meaning I want to be like "=COUNTIF(A1!$I:$I,A2)"

Thanks

2 Replies

@Jonathan_Spring 

 

With your very brief example, I'm going to suggest you do some research on the applicability of the INDIRECT function. It sounds like part of the solution. But you might need to make some changes to where you place some of those data points. Here's a place where you can start your research.

https://exceljet.net/excel-functions/excel-indirect-function

 

@Jonathan_Spring 

=COUNTIF(INDIRECT("'"&A1&"'!"&"I:I"),B1)

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.