In sheet1
have a column with a list of IDs, each with a set of statuses:
ID Apr May Jun
1 Load Load Complete
2 Load Complete NA
3 Load Load Load
In a separate sheet, sheet2 I have a list of the IDs and I am trying to show which of those are complete. My thinking is to use countif
with a dynamic cell range using the address
function:
=countif("'sheet1'!"&address(row(index('sheet1'!$A$1:$A$5,match(A2,'sheet1'!$A$1:$A$5,0),1)),3)&":"&ADDRESS(row(index('sheet1'!$A$1:$A$5,match(A5,'sheet1'!$A$1:$A$500,0),1)) 1,26),"Completed")
This formula should find the relevant row for each Id and then check to see whether there are any values of 'Completed' in that row. When combined with an if
function, this should give an output of the following in sheet2
:
ID Complete
1 Y
2 Y
3 N
But at the moment, it is not registering any as complete as the countif formula is returning a 0 for every row.
Is this because countif
cannot handle dynamic ranges using address
? If there is a better way to do this, please let me know.
CodePudding user response:
Given your data, please try the following
=INDEX({A2:A,IF(A2:A="","",
(IF(REGEXMATCH(B2:B&C2:C&D2:D,"Complete")=TRUE,"Y","N")))})
(As always, please adjust ranges to your needs)
Functions used: