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