Formula help with indirect function.

[omairhe]

Hello all,

I have a formula that will check for duplicate values in column G.

=SUMPRODUCT((COUNTIF(G5:G1000,G5:G1000)-1)*(G5:G1000<>""))>0

Thing is I want it to check for duplicate values in not just one sheet but in any sheet which is currently active. Hence I have another formula for that in cell.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

So how can I use the indirect function to look for the duplicate values from the sheet name given from the second formula and the range to be taken is G5:G1000

I will appreciate any help on this.

Thanks.

 

[ExcelGzh]

If the formula that produces the sheet name is in cell A1 then your formula would look like this

=SUMPRODUCT((COUNTIF(INDIRECT("'"&A1&"'!$G5:$G1000",TRUE),INDIRECT("'"&A1&"'!$G5:$G1000",TRUE))-1)*(INDIRECT("'"&A1&"'!$G5:$G1000",TRUE)<>""))>0

The reason for the single quotes before and after the A1 reference is in case the sheet name has one or more spaces in it.

As a general comment on the INDIRECT function, it only contains cell references. Any function names, eg SUMPRODUCT or COUNTIF are outside the INDIRECT function.

 

[omairhe]

If the formula that produces the sheet name is in cell A1 then your formula would look like this

=SUMPRODUCT((COUNTIF(INDIRECT("'"&A1&"'!$G5:$G1000",TRUE),INDIRECT("'"&A1&"'!$G5:$G1000",TRUE))-1)*(INDIRECT("'"&A1&"'!$G5:$G1000",TRUE)<>""))>0

The reason for the single quotes before and after the A1 reference is in case the sheet name has one or more spaces in it.

As a general comment on the INDIRECT function, it only contains cell references. Any function names, eg SUMPRODUCT or COUNTIF are outside the INDIRECT function.

I have an upcoming task that involves the Indirect function. I'll keep that in mind and thanks for the tip.