Here's what I'm looking for. Sample file attached, rows/columns in this write may have changed.
I've created a sort of gannt chart in Excel. I'm looking to find the first and last non-blank values in row 2 and then find the corresponding value in row 1. The value from row 1 would be populated in row 2, further left of the range I'm searching for non-blank cells.
Row 1 data (start in col c) 1 1 2 2 3 4 4 5 6 6
Row 2 data (start in col c) 3 7 5 5 5 7 (2 blank cells to start under the 1's from row 1 and 2 blank cells under the 6's from row 1)
Col A should be able to return the 2 (first non blank is 3) and Col B should return 5 (last non-blank is 7).
I think I have a good formula to find the first non blank cell and return the value from row 1. I'm using:
=IF(COUNTA(C2:M2),INDEX(C$4:M$4,MATCH(TRUE,INDEX(C2:M2<>"",0),0)),"")
What I need is a formula to find the LAST non-blank and return the row 1 value (no VBA). Any help is greatly appreciated.
Thank you.
Bookmarks