How to make get the date above a cell with 1

[balsamatora]

Dear Community,

Could you please help me out?


I am trying to make cell E37 be populated with the date above which there is a "1" on row 37. In other words it should populate cell E37 with 30 06 2034. Please let me know what formula to use for cell E37.

 

[Joe4]

Welcome to the Board!

What columns, exactly, are we looking at for value of 1?
We cannot tell from your image, as you cut off the column headers!

 

[balsamatora]

Welcome to the Board!

What columns, exactly, are we looking at for value of 1?
We cannot tell from your image, as you cut off the column headers!

Thank you!

Apologies, I have uploaded the screenshot below again, should be visible now.

 

[Joe4]

I cannot see anything after column FG, because your image gets chopped off there.
How far out does it go?

 

[balsamatora]

I cannot see anything after column FG, because your image gets chopped off there.
How far out does it go?

It goes quite a bit - to column OR

 

[Joe4]

OK, I originally did not understand your question fully originally (I thought you wanted to copy the value from the cell above E37, not the cell above the "1"), but I see now.
However, that leads to a whole new question.

In rows where you have multiple values of 1, which date do you want to put in column E?
For example, row 35 has a whole bunch of values of 1 in it. So which date do you choose to put in cell E35?

 

[balsamatora]

OK, I originally did not understand your question fully originally (I thought you wanted to copy the value from the cell above E37, not the cell above the "1"), but I see now.
However, that leads to a whole new question.

In rows where you have multiple values of 1, which date do you want to put in column E?
For example, row 35 has a whole bunch of values of 1 in it. So which date do you choose to put in cell E35?

Thanks

Other rows are fine as they are. Question is only related to row 37.

Basically, on row 37, there is only one cell with value 1, all other cells have value of zero. I'd like to have a formula, where it sees in which column the "1" value is, and then takes the date above it, from row 32.

In practice, the formula should recognize the "1" value on FF37 and insert the date on row 32 (i.e. 30-06-2024) in cell E37.

 

[Joe4]

Thanks

Other rows are fine as they are. Question is only related to row 37.

Basically, on row 37, there is only one cell with value 1, all other cells have value of zero. I'd like to have a formula, where it sees in which column the "1" value is, and then takes the date above it, from row 32.

In practice, the formula should recognize the "1" value on FF37 and insert the date on row 32 (i.e. 30-06-2024) in cell E37.

OK, then try this in cell E37:

=INDEX(EK$32:OR$32,MATCH(1,EK37:OR37,0))

 

[balsamatora]

OK, then try this in cell E37:

=INDEX(EK$32:OR$32,MATCH(1,EK37:OR37,0))

Thank you very much.

It works like a clockwork.

 

[Joe4]

You are welcome.
Glad I was able to help!