Auto-calculation of duplicates based on variable in drop down list

[Danger_SF]

Good evening, all.

How would I modify the formula in cell C3 on Tab 3 if I wanted it to auto-calculate based on the variable in the drop-down list in C1 on the same tab?

[#2]

@Danger_SF That would be:

=SUM(--(COUNTIF(INDIRECT(C1&"!B:B"),UNIQUE(INDIRECT(C1&"!B:B")))>1))*2

[#3]

@Riny_van_Eekelen 

Thank you sir. I haven't had time to try this because I've been so swamped at work but I at least wanted to stop in to say thank you for taking the time to respond. 

Just out of curiosity, what is the logic of the INDIRECT command? How does this tell the formula to go pull data from a different tab? Thanks in advance. 

[#4]

@Danger_SF Let's say you selected SF in the dropdown, then the INDIRECT part of the formula evaluates to INDIRECT(SF!B:B). This has the same effect as it you had typed SF!B:B directly. Thus INDIRECT allows you to construct a textual cell reference based on values in other cells.

Another example. Let's say, A1 contains the text string "B1:B10", the formula =SUM(INDIRECT(A1)) will evaluate to =SUM(B1:B10).

Similarly, if A1 contains the word "myData", the formula evaluates to =SUM(myData) where myData would be a valid named range.

Lastly, you can't construct an INDIRECT reference to a closed workbook.

[#5]

I see. Thank you for the explanation.

So how would the formula be modified to reference column entries on a tab instead of referencing the tab itself? In other words, from Tab "USE", using the drop-down list, I'm trying to see how many address number references are duplicates in the city of SF.

[#6]

@Danger_SF Not clear to me what exactly you want to achieve, but if you first create the formula with the direct references you see exactly how the link should look like. Then you can construct that link by joining the pieces together with pieces of text, the &-sign and cell references.

[#7]

Can you reference the revised file I uploaded to see what I'm trying to achieve? I don't know how to explain it as well as show it.

[#8]

@Danger_SF OK, I gave it another look and took the liberty to make some changes so that you better use Excel's table and dynamic array capabilities. 

Using structured table references eliminates the need to use INDIRECT. As long as you know the table and column name, it doesn't matter on which tab in the workbook that table sits. Furthermore, use two helper formulas. One that dynamically feed the data validation list and one that filters out all addresses for the selected city. Hide these if you want.

[#9]

@Riny_van_Eekelen 

Thank you again for your patience and cooperation.

If possible, I'd rather not bring the data over to the USE tab before I count duplicates.

Is there any way to have the formula in cell B3 on the USE tab count the duplicates that exist within columns B and C on the SITES tab, depending on what value is in cell B1 on the USE tab?

Essentially a formula that provides the answer to:

Whatever value exists in tab USE: cell B1, count duplicates within tab SITES: columns B and C which consist of that value.

[#10]

@Danger_SF Sorry for being slow, but I don't follow. The data is still in the SITES sheet, but in a structured table. That enables you to call for the table and column names without having to bother about the sheet name SITES and columns references B and C.

[#11]

Thank you again.