Forum Discussion
Auto-calculation of duplicates based on variable in drop down list
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.
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.
- 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.
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.
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.