Excel can either have a formula, or a hard-coded value in a cell, and one cannot turn to the other without it being done manually, or by VBA.
So I think you answer requires VBA. You would need something like a date stamp to capture when values are placed in certain cells.
If you are open to a VBA solution, we can probably help you, but you will need to be VERY specific in the details, being sure to indicate exactly what ranges are involved, and when/how things should happen.
I tried combining these formulas:
LOOKUP(2,1/(ISNUMBER(A1:A100)),A1:A100)
=IFERROR(INDEX(B12:F12,MATCH(TRUE,B12:F12<>"",0)),"")
with offset, but I get an error. I found this thread a thread that mentions: "
The <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Reference</code>
is the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">OFFSET</code>
function refers to a Range object (a cell). The result of your <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Lookup</code>
function is a numeric value, in this case 5. You can't <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">OFFSET</code>
a numeric value."
Link to thread: https://stackoverflow.com/questions/28329954/combining-lookup-and-offset
So I'm guessing that happens with both LOOKUP and INDEX_MATCH, and the only solution is VBA...
I will work on an example with all the details to see what answers I can get.