dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi Folks,
I have the following worksheets: ‘Table' worksheet and ‘data1’ worksheet.
I am looking to design a formula to 'count the number of unique values' in column A of the 'data1' worksheet.
The ‘Table Worksheet’ contains ‘table 1’ with ‘column A’ listing my worksheet names. My goal is to put a formula in column B of the ‘table 1’ that refers to ‘worksheet names’ within column A of ‘table 1’ and then calculates the number of unique values in Column A of the worksheet being referred to (column A in data1 has cell A1 as a header which I do not wish to be included in the count).
E.g. in Table 1: if cell A2 contains ‘Data1’ (the name of the first worksheet), in B2 I would like to put in a formula that reads the contents of cell A2 (‘Data1’) and refers to the worksheet data1 before calculating and returning the number unique values in column A of the ‘Data1’ worksheet.
I found a formula for calculating the number of unique values within a column:
I also found a formula for referring to Cell A2 in Table 1
However, I have tried combining these formulas but cannot get it to work.
Any help would be much appreciated!
Kind regards,
Doug
I have the following worksheets: ‘Table' worksheet and ‘data1’ worksheet.
I am looking to design a formula to 'count the number of unique values' in column A of the 'data1' worksheet.
The ‘Table Worksheet’ contains ‘table 1’ with ‘column A’ listing my worksheet names. My goal is to put a formula in column B of the ‘table 1’ that refers to ‘worksheet names’ within column A of ‘table 1’ and then calculates the number of unique values in Column A of the worksheet being referred to (column A in data1 has cell A1 as a header which I do not wish to be included in the count).
E.g. in Table 1: if cell A2 contains ‘Data1’ (the name of the first worksheet), in B2 I would like to put in a formula that reads the contents of cell A2 (‘Data1’) and refers to the worksheet data1 before calculating and returning the number unique values in column A of the ‘Data1’ worksheet.
I found a formula for calculating the number of unique values within a column:
Code:
=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))
I also found a formula for referring to Cell A2 in Table 1
Code:
=INDIRECT(“’”&$A2&”’!”&A:A)
However, I have tried combining these formulas but cannot get it to work.
Any help would be much appreciated!
Kind regards,
Doug