Return a Date based on the first Non-Blank value in a row

[RLegGrand]

Hello! I haven't used this forum in many, many years, but am back using Excel again and so here I am... albeit a bit rusty. Thanks for your patience.

I have a series of sequential dates in Row.1

Col.C-End I have the number of engineers assigned to a unique task on any given day.

Col.B Is the name of the task

Col.A is where I need a formula that looks for the first non-blank cell in the same row (the first date an engineer is assigned) and returns the date from Row.1 in that column.

I assume this will use some sort of Indirect statement? I can use a Match/Array formula to get the value of the first instance, but can't figure out how to pull the date from Row.1?

Thanks for your help!

 

[Rastaman]

Perhaps something like this:

=OFFSET($A$1,0,MIN(IF(C2:N2<>"",COLUMN(C2:N2)))-1,1,1)

confirmed with <ctrl><shift><enter>

Rick

 

[RLegGrand]

Hmmmm… Gives me an error.

 

[RLegGrand]

I think I see what your formula suggests, but getting an error still.

For clarity on my dataset C2:F5 (abbreviated range) contains the number of engineers assigned to a unique task each day. HOWEVER, in the four days of this example, they could be assigned to any possible variation, one day, three days, no days, alternate days.

Again, I need to find the date of the FIRST instance, but the row will have an inconsistent number of blanks vs. entries.

Hoping to solve for A:A

A B C D E F
1 Start Date Title 1/1 1/2 1/3 1/4
2 01/02/2019 Example A 1
3 01/02/2019 Example B 1 1
4 01/03/2019 Example C 4
5 01/04/2019 Example D 2

Thanks Again!

 

[Rastaman]

Sorry, my response got cut off. This is a CSE, or Array, formula. Instead of confirming with Enter, you need to confirm with control-shift-enter.

If I understand your spreadsheet, cells C1:N1 (or whatever you last column is) contains the sequential dates. Rows 2 downward is a list of names in column B. And you want column A to show the date in row 1 of the first non-blank cell in that row.

Rick

 

[RLegGrand]

Ahh, my oversight - That makes it work!!

Thanks so much

 

[Rastaman]

Glad I could help. Sorry for the earlier confusion.