First non-empty cell from a row array returns the value of the cell above

[Crane]

Hello Excel Gurus,

I need your help, please. I am trying to create a time plan in Excel. I got two columns, one says "Start", the other "End". I'd like to have these two cells automatically filled out when a first non-empty cell is detected in an array - i want it to pick up or "echo" the value of the cell I choose. Please check this table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]8.5[/TD]
[TD]8.6[/TD]
[TD]8.7[/TD]
[TD]8.8[/TD]
[TD]8.9[/TD]
[TD]8.10[/TD]
[/TR]
[TR]
[TD]Site 1[/TD]
[TD]8.5[/TD]
[TD]8.8[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 2[/TD]
[TD]8.7[/TD]
[TD]8.10[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Site 3[/TD]
[TD]8.6[/TD]
[TD]8.8[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 4[/TD]
[TD]8.9[/TD]
[TD]8.10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much in advance.

 

[Joe4]

Welcome to the Board!

I think this is more difficult than it needs to be because of how you have set up the data.
In mathematical terms, 8.10 is less than 8.5. So you cannot easily use basic mathematical formulas, unless you were to change your data.
Is there any ability to change the structure, so the values are like: 8.05, 8.06, 8.07, 8.08, 8.09, 8.10, so that simple math can be used?

 

[Crane]

Many thanks, Joe4, for the feedback.

Actually the numbers are dates. 8.10 means August 10 and so on. What I'd like to happen is that the first non-empty cell in the row array triggers the upper cell to be "echoed" or shown in the "start" cell. Same for the "End" cell being the last non-empty cell in the array. It should look like this (column 2 and 3 with the formula to fetch value, which is the date, above when a non-empty cell in an array is detected):

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]August 5[/TD]
[TD]August 6[/TD]
[TD]August 7[/TD]
[TD]August 8[/TD]
[TD]August 9[/TD]
[TD]August 10[/TD]
[/TR]
[TR]
[TD]Site 1[/TD]
[TD]August 5[/TD]
[TD]August 8[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 2[/TD]
[TD]August 7[/TD]
[TD]August 10[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Site 3[/TD]
[TD]August 6[/TD]
[TD]August 8[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 4[/TD]
[TD]August 9[/TD]
[TD]August 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Are all those dates actual date entries?
If they are, and they are not text, then we can use formulas like this (assuming the data grid above is in ranges A1:I5):
enter in cell D2:

=IF(AND(D$1>=$B2,D$1<=$C2),"x","")

and copy down to I5

 

[Crane]

Sorry for the confusion, Joe4 and thanks once again for your help here.

Based on your formula above, it will fill out cells D2 to I5. The opposite is actually what I'm trying to accomplish. D2 to I5 will manually be filled out depending on the timeline of the site. For example, on the 2nd row, when I put an "x" in cell E2 until I2, cell B2 should show the date August 6 as it is the first non-empty cell in that row array. Cell C2 on the other hand, will show August 10 because the last non-empty cell in that row array is cell I2.

So objective is to actually have the values of cell B2 to C5 to automatically be filled out with the date based on the first and last non-empty cell in a given row array.

 

[Joe4]

OK. That is much harder. I did a bunch of Google Searches and was able to cobble these formulas together for you (so please don't ask him how they work, I just played around with solutions I found on the internet).

Start (place in cell B2 and copy down to B5):

=IF(COUNTA(D2:I2),INDEX(D$1:I$1,MATCH(TRUE,INDEX(D2:I2<>"",0),0)),"")

End (place in cell C2 and copy down to C5):

=OFFSET(INDIRECT(ADDRESS(ROW(), LOOKUP(2, 1/(2:2<>""), COLUMN(1:1)))),1-ROW(),0,1,1)

There may be simpler options (especially with the second one), but these seem to work!

 

[Crane]

Can't thank you enough, Joe4. I haven't tried it as I'm away from my PC but I get the feeling formula above will fix my trouble. I'll keep you posted!

 

[Crane]

Start works really well! Thanks a lot!

I am having problems with the End one and I am not quite sure what part of it to edit. My cell C2 in the actual spreadsheet is U20, running until U216. Which part of the script should I replace given this?

 

[Peter_SSs]

I don't have your layout, but keeping the layout Joe used, and his formula in column B, try this one for column C & see if you can adapt to your layout.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Start	End	5 Aug	6 Aug	7 Aug	8 Aug	9 Aug	10 Aug
2	Site 1	5 Aug	8 Aug	x	x	x	x
3	Site 2	7 Aug	10 Aug			x	x	x	x
4	Site 3	6 Aug	8 Aug		x	x	x
5	Site 4
6	Site 5	9 Aug	10 Aug					x	x
Start End Dates

 

[Crane]

You guys are amazing! Thank you, Peter, Joe! Formula for "end" part works flawlessly! Now my plan is complete

Please if anyone can close this thread, issue resolved!