Forum Discussion
Auto-calculation of duplicates based on variable in drop down list
Danger_SF That would be:
=SUM(--(COUNTIF(INDIRECT(C1&"!B:B"),UNIQUE(INDIRECT(C1&"!B:B")))>1))*2
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.
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.
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.