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

RLegGrand

New Member
Joined
Feb 15, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
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

New Member
Joined
Feb 15, 2019
Messages
4
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

Board Regular
Joined
Feb 18, 2006
Messages
236

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,542
Messages
5,529,454
Members
409,878
Latest member
DDhol
Top