Hi all,

How is everyone doing? Well I have been exhausted with a workbook I have been working on for quite some time now, and hope I can get some help here.

I have the following array formula in Sheet1, S2 and copied down to S10051:

{=IF('Sheet2'!$AP$5="",IF(ROWS($A$2:$A2)>T$1,"",INDEX($G$2:$G$10051,SMALL(IF($K$2:$K$10051=O$1,IF($Y$2:$Y$10051=S$1,ROW($K$2:$K$10051)-ROW($K$2)+1)),ROWS($A$1:$A1)))),"")}
I am trying to have the ‘Sheet2’!$AP$5=”” portion to be relative to any changed cell in range H5:H15001 of Sheet2 whose value changes. For example, when cell H15 is changed, the formula should change to {=IF(‘Sheet2’!$AP$15=”” . . .; when cell H200 is changed, the formula should change to {=IF(‘Sheet2’!$AP$200=”” . . . etc. Concurrently, I want the value of that changed cell place in cell O1 of Sheet1, and have the workbook be updated.

Nothing I tried is working.

Any help will be much appreciated.

Gos-C