I have a google sheet where I am trying to identify buy and sell dates for stocks, based on a value in a column.
My first formula in AA3 is =INDEX($A$30:$A$300 , MATCH(TRUE(),$K$30:$K$300 < 30 , 0) , 1)
AB3 identifies the ROW of that value.
I'd like the "sell" formula in AC3 to do something similar as but use the row in AB3 as the starting point of the index match. Like:
=INDEX($A$38:$A$300 , MATCH(TRUE(),$K$38:$K$300 > 65 , 0) , 1)
In this example, it would be ROW 41.
And then the next buy formula in AA4 to be for the next buy signal after the sell signal that happened in row 41. Like
=INDEX($A$41:$A$300 , MATCH(TRUE(),$K$41:$K$300 < 30 , 0) , 1)
And the next sell signal formula to be after whatever row the buy signal happened in.
I'm having issues trying to modify the starting point in the INDEX and MATCH to reflect a changing starting cell number. I get formatting errors trying to use indirect or concat to make that starting cell number based on a value in another cell.
Thx for any help!