I have been playing around with indirect trying to get what I need from excel.
My current formula works flawlessly off one sheet:
=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())
<o
>I am trying to get it to work off several sheets now which isn't working so well. I am no expert by the way so I am trying everything I understand at the moment.</o>
<o>Here are a couple of things I have tried so far:</o>
<o>=IF(COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1,””,ROW())<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o>=SUMPRODUCT(COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1,””,ROW())
"J-3:J-4" are the sheets. From what I have read from the indirect function should be grabbing those then processing the rest of the function which B2 through D41 are my cells in the sheets.
When I run the Function Arguments on both function excel tells me:
Logical Test COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1 = Invalid
Value if true "" = #NAME?
Value if false ROW() = {1}
I am not sure where to go from here.
Thank you
</o></o>
My current formula works flawlessly off one sheet:
=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())
<o
>I am trying to get it to work off several sheets now which isn't working so well. I am no expert by the way so I am trying everything I understand at the moment.</o><o
>Here are a couple of things I have tried so far:</o><o
>=IF(COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1,””,ROW())<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o><o
>=SUMPRODUCT(COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1,””,ROW())"J-3:J-4" are the sheets. From what I have read from the indirect function should be grabbing those then processing the rest of the function which B2 through D41 are my cells in the sheets.
When I run the Function Arguments on both function excel tells me:
Logical Test COUNTIF(INDIRECT(“J-3:J-4”)!B$2$:B$41,A1)>=1 = Invalid
Value if true "" = #NAME?
Value if false ROW() = {1}
I am not sure where to go from here.
Thank you
</o
></o>
