Excel Formula issue: Counting the number of unique values in a column in another worksheet referred in the formula by the value in another cell

[dougmarkham]

Hi Folks,

I have four worksheets in my DataSplitter Workbook---P0817A, P0817B, P0817C and P0817D---that were generated by splitting data from a central report. Each of these worksheets contains store names in column A that will receive an order.

https://drive.google.com/file/d/1TgCyVeHAXrc48Zhhx3QWHkza1luFJ9Sl/view?usp=sharing

In a fifth worksheet in my DataSplitter wb---DISTRIBUTION ORDER REQUEST---I have a table which needs to generate a count of the number of unique text entries in existing in Column A of worksheets P0817A, P0817B, P0817C and P0817D, which is recorded in the table shown in the link below.

https://drive.google.com/file/d/1gVDY5KP8Lzbh8_V37P9xLjcRiO5aTmYQ/view?usp=sharing

I have found a nice formula---to go into cell "C26" of worksheet "DISTRIBUTION ORDER REQUEST"---that calculates the number of unique text entries in column A:

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

The problem is that I need to refer to the worksheet's location of each column A i.e., in this instance, for worksheets P0817A, P0817B, P0817C and P0817D. Every month this report is run, the names of the split worksheets must differ. For instance, P0817A refers to a report generated for August 2017. Ergo I have used a macro to put the Worksheet names e.g., P0817A, P0817B, P0817C and P0817D into Cells A26:A29 of the worksheet DISTRIBUTION ORDER REQUEST, so that I can refer to them e.g., using cell A26 to define the worksheet that Column A is located.

Sounds complicated, so here is an example of something I've got working. I already have a formula (in cell D26) that counts to total number of text entries (not unique entries):

=IF(A26="","",COUNTA(INDIRECT("'"&A26&"'!A:A"))-1)

What I'm looking to do is to refer to A26, A27, A28 etc., within

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

so that I can calculate the number of stores for each zone (A, B, C, D) within the report for August.

Would you please help me modify this SUMPRODUCT formula to achieve this?

Kind regards,

Doug.

 

[dougmarkham]

I tried this modification, but I get #NAME error.

SUMPRODUCT((INDIRECT(“’”&$A26&”’!”&(ADDRESS(2,1)&":"&ADDRESS(5000,1)))<>"")/COUNTIFS(INDIRECT(“’”&$A26&”’!”&(ADDRESS(2,1)&":"&ADDRESS(5000,1))),INDIRECT(“’”&$A26&”’!”&(ADDRESS(2,1)&":"&ADDRESS(5000,1)))&""))