Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.

[namayatrell7]

Hi all,

So my problem relates to trying to figure out the next date that any given employee is due to rotate into my section of the laboratory. In the picture provided, I have a table with the dates for the Monday for each week as the column headers, names as the row headers and then either blank or letter-filled cells populating the table. Under this I have a statistics section and I would like to have the next date somebody is due in by looking up the column headers for the next date after today and relating this to the next non-blank cell in the row and returning the date that is there in the output cell.

The letters used in the table are in the key - R, N, M and C.

If anyone can help I would be hugely appreciative - and even more so if you can help me understand how the formula you use works so I can learn.

Many thanks!

Namaya

 

[bobaol]

hello, try this... here is an image of the results, and below are the formulas. copy the entire grid and paste it to your spreadsheet in cell A1. It should automatically turn it into formulas in your spreadsheet. you might have to format the dates in row3, and other areas where it is a date. here's the logic... rows rows 3 to 9, you manually enter. cell C12 is today's date, so you have something to reference against. I'll come back to D14 to D19 later. in part1, rows 24 to 29, for example, in cell C24, i ask if cell C4 is blank. if it is blank, put in blank using quote quote, if it is not blank, bring in cell C$3. (the dollar means the 3 never changes, but there is no dollar in C, so when you copy and paste the formula, it will be D$3, E$3, and so on. The $3, the 3 will always be 3 no matter where it is copied to.) so, in part1, it asks if it is blank or not. if not blank, then bring in the date, regardless of what the date is. In Part2, it takes the date in part1 and compares it against today's date in cell C12. Let's take for example, cell C34. it says if cell C24 is blank, then let it be blank. but if cell C34 is less than today's date in cell C12, then let it be blank. otherwise, give me the date from cell C24. in this case, the cell in C24 is less than today's date of 29-march-2024, therefore, the result is blank. contrast cell C34 to cell I34. I34 is bigger than today's date, so it brings in the value from cell I24. In part3, it brings in the minimum value of part2. for example, name5 and name6 have two dates each. but you only want the minimum of those dates because the minimum is the closest to today's date. Now, in part3, you have one value for name1 to name6. Finally, after all this is done, look at cells D14 to D19. Cells D14 to D19 is a simple reference to cells C44 to C49. the key here is that you can use a lot of the spreadsheet, do your calculations in simple terms, ask one calculation, find the answer, then ask another calculation, find that answer, and so forth, until you get your answer. it is much easier to ask one calculation at a time, find the answer to that one calculation, then ask the next calculation. finally, notice all the data is structured in rows 4 to 9, and variations of 4 to 9. when you are able to have a simple relative reference, it makes life easier when trying to figure out what is where. I structured it so that name1 is always on row4, and name6 is always on row9. hope this helps. cheers!

'cell A1

Staff Rotation - Blood Transfusion

Week Beginning:

Employee

45355

=C3+7

=D3+7

=E3+7

=F3+7

=G3+7

=H3+7

=I3+7

=J3+7

=K3+7

=L3+7

=M3+7

=N3+7

Name 1

R

R

Name 2

R

R

Name 3

R

R

Name 4

R

R

Name 5

R

R

Name 6

R

R

Statistics

Today's Date:

=NOW()

Employee

# Rotations

Next Due in

Name 1

2

=C44

Name 2

2

=C45

Name 3

2

=C46

Name 4

2

=C47

Name 5

2

=C48

Name 6

2

=C49

'formulas for you to work with - part1

Employee

Name 1

=IF(C4="","",C$3)

=IF(D4="","",D$3)

=IF(E4="","",E$3)

=IF(F4="","",F$3)

=IF(G4="","",G$3)

=IF(H4="","",H$3)

=IF(I4="","",I$3)

=IF(J4="","",J$3)

=IF(K4="","",K$3)

=IF(L4="","",L$3)

=IF(M4="","",M$3)

=IF(N4="","",N$3)

Name 2

=IF(C5="","",C$3)

=IF(D5="","",D$3)

=IF(E5="","",E$3)

=IF(F5="","",F$3)

=IF(G5="","",G$3)

=IF(H5="","",H$3)

=IF(I5="","",I$3)

=IF(J5="","",J$3)

=IF(K5="","",K$3)

=IF(L5="","",L$3)

=IF(M5="","",M$3)

=IF(N5="","",N$3)

Name 3

=IF(C6="","",C$3)

=IF(D6="","",D$3)

=IF(E6="","",E$3)

=IF(F6="","",F$3)

=IF(G6="","",G$3)

=IF(H6="","",H$3)

=IF(I6="","",I$3)

=IF(J6="","",J$3)

=IF(K6="","",K$3)

=IF(L6="","",L$3)

=IF(M6="","",M$3)

=IF(N6="","",N$3)

Name 4

=IF(C7="","",C$3)

=IF(D7="","",D$3)

=IF(E7="","",E$3)

=IF(F7="","",F$3)

=IF(G7="","",G$3)

=IF(H7="","",H$3)

=IF(I7="","",I$3)

=IF(J7="","",J$3)

=IF(K7="","",K$3)

=IF(L7="","",L$3)

=IF(M7="","",M$3)

=IF(N7="","",N$3)

Name 5

=IF(C8="","",C$3)

=IF(D8="","",D$3)

=IF(E8="","",E$3)

=IF(F8="","",F$3)

=IF(G8="","",G$3)

=IF(H8="","",H$3)

=IF(I8="","",I$3)

=IF(J8="","",J$3)

=IF(K8="","",K$3)

=IF(L8="","",L$3)

=IF(M8="","",M$3)

=IF(N8="","",N$3)

Name 6

=IF(C9="","",C$3)

=IF(D9="","",D$3)

=IF(E9="","",E$3)

=IF(F9="","",F$3)

=IF(G9="","",G$3)

=IF(H9="","",H$3)

=IF(I9="","",I$3)

=IF(J9="","",J$3)

=IF(K9="","",K$3)

=IF(L9="","",L$3)

=IF(M9="","",M$3)

=IF(N9="","",N$3)

'formulas for you to work with - part2

Employee

Name 1

=IF(C24="","",IF(C24<$C$12,"",C24))

=IF(D24="","",IF(D24<$C$12,"",D24))

=IF(E24="","",IF(E24<$C$12,"",E24))

=IF(F24="","",IF(F24<$C$12,"",F24))

=IF(G24="","",IF(G24<$C$12,"",G24))

=IF(H24="","",IF(H24<$C$12,"",H24))

=IF(I24="","",IF(I24<$C$12,"",I24))

=IF(J24="","",IF(J24<$C$12,"",J24))

=IF(K24="","",IF(K24<$C$12,"",K24))

=IF(L24="","",IF(L24<$C$12,"",L24))

=IF(M24="","",IF(M24<$C$12,"",M24))

=IF(N24="","",IF(N24<$C$12,"",N24))

=IF(O24="","",IF(O24<$C$12,"",O24))

Name 2

=IF(C25="","",IF(C25<$C$12,"",C25))

=IF(D25="","",IF(D25<$C$12,"",D25))

=IF(E25="","",IF(E25<$C$12,"",E25))

=IF(F25="","",IF(F25<$C$12,"",F25))

=IF(G25="","",IF(G25<$C$12,"",G25))

=IF(H25="","",IF(H25<$C$12,"",H25))

=IF(I25="","",IF(I25<$C$12,"",I25))

=IF(J25="","",IF(J25<$C$12,"",J25))

=IF(K25="","",IF(K25<$C$12,"",K25))

=IF(L25="","",IF(L25<$C$12,"",L25))

=IF(M25="","",IF(M25<$C$12,"",M25))

=IF(N25="","",IF(N25<$C$12,"",N25))

=IF(O25="","",IF(O25<$C$12,"",O25))

Name 3

=IF(C26="","",IF(C26<$C$12,"",C26))

=IF(D26="","",IF(D26<$C$12,"",D26))

=IF(E26="","",IF(E26<$C$12,"",E26))

=IF(F26="","",IF(F26<$C$12,"",F26))

=IF(G26="","",IF(G26<$C$12,"",G26))

=IF(H26="","",IF(H26<$C$12,"",H26))

=IF(I26="","",IF(I26<$C$12,"",I26))

=IF(J26="","",IF(J26<$C$12,"",J26))

=IF(K26="","",IF(K26<$C$12,"",K26))

=IF(L26="","",IF(L26<$C$12,"",L26))

=IF(M26="","",IF(M26<$C$12,"",M26))

=IF(N26="","",IF(N26<$C$12,"",N26))

=IF(O26="","",IF(O26<$C$12,"",O26))

Name 4

=IF(C27="","",IF(C27<$C$12,"",C27))

=IF(D27="","",IF(D27<$C$12,"",D27))

=IF(E27="","",IF(E27<$C$12,"",E27))

=IF(F27="","",IF(F27<$C$12,"",F27))

=IF(G27="","",IF(G27<$C$12,"",G27))

=IF(H27="","",IF(H27<$C$12,"",H27))

=IF(I27="","",IF(I27<$C$12,"",I27))

=IF(J27="","",IF(J27<$C$12,"",J27))

=IF(K27="","",IF(K27<$C$12,"",K27))

=IF(L27="","",IF(L27<$C$12,"",L27))

=IF(M27="","",IF(M27<$C$12,"",M27))

=IF(N27="","",IF(N27<$C$12,"",N27))

=IF(O27="","",IF(O27<$C$12,"",O27))

Name 5

=IF(C28="","",IF(C28<$C$12,"",C28))

=IF(D28="","",IF(D28<$C$12,"",D28))

=IF(E28="","",IF(E28<$C$12,"",E28))

=IF(F28="","",IF(F28<$C$12,"",F28))

=IF(G28="","",IF(G28<$C$12,"",G28))

=IF(H28="","",IF(H28<$C$12,"",H28))

=IF(I28="","",IF(I28<$C$12,"",I28))

=IF(J28="","",IF(J28<$C$12,"",J28))

=IF(K28="","",IF(K28<$C$12,"",K28))

=IF(L28="","",IF(L28<$C$12,"",L28))

=IF(M28="","",IF(M28<$C$12,"",M28))

=IF(N28="","",IF(N28<$C$12,"",N28))

=IF(O28="","",IF(O28<$C$12,"",O28))

Name 6

=IF(C29="","",IF(C29<$C$12,"",C29))

=IF(D29="","",IF(D29<$C$12,"",D29))

=IF(E29="","",IF(E29<$C$12,"",E29))

=IF(F29="","",IF(F29<$C$12,"",F29))

=IF(G29="","",IF(G29<$C$12,"",G29))

=IF(H29="","",IF(H29<$C$12,"",H29))

=IF(I29="","",IF(I29<$C$12,"",I29))

=IF(J29="","",IF(J29<$C$12,"",J29))

=IF(K29="","",IF(K29<$C$12,"",K29))

=IF(L29="","",IF(L29<$C$12,"",L29))

=IF(M29="","",IF(M29<$C$12,"",M29))

=IF(N29="","",IF(N29<$C$12,"",N29))

=IF(O29="","",IF(O29<$C$12,"",O29))

'formulas for you to work with - part3

Employee

Name 1

=MIN(C34:O34)

Name 2

=MIN(C35:O35)

Name 3

=MIN(C36:O36)

Name 4

=MIN(C37:O37)

Name 5

=MIN(C38:O38)

Name 6

=MIN(C39:O39)

 

[Alex Blakenburg]

Since you have MS 365 you can use XLookup. See if this helps.
The first / inner XLookup looks up the name and returns the whole row. This is a bit more obvious if you throw the let function into the mix so I have included that as an option.
The outer XLookup then looks at the row for the first non blank cell starting from the right.
The -1 tells it to look from Last to First (right to left) and the condition will evaluate as True or False hence the Lookup up value used is True.

Set the column range to be more columns than you think you will ever use. We generally prefer not to set it to use the whole row since a lot of lookups using whole column and/or whole row references can slow down the performance.

20240330 Lookup Last Used namayatrell7.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2			Week beginning:
3		Employee	1/04/2024	8/04/2024	15/04/2024	22/04/2024	29/04/2024	6/05/2024	13/05/2024	20/05/2024	27/05/2024
4		Name 1	R						C
5		Name 2		R
6		Name 3			R
7		Name 4				R
8		Name 5					R
9		Name 6						R
10
11
12			Employee	# Rotations	Next due in	Using Let
13			Name 1	2	13/05/2024	13/05/2024
14			Name 2	2	8/04/2024	8/04/2024
15			Name 3	2	15/04/2024	15/04/2024
16			Name 4	2	22/04/2024	22/04/2024
17			Name 5	2	29/04/2024	29/04/2024
Sheet1

Cell Formulas
Range	Formula
D3:K3	D3	=C3+7
E13:E17	E13	=XLOOKUP(TRUE, XLOOKUP($C13,$B$3:$B$9,$B$3:$Z$9,"") <>"",$B$3:$Z$3,"",0,-1)
F13:F17	F13	=LET(getNameRow,XLOOKUP($C13,$B$3:$B$9,$B$3:$Z$9,""), XLOOKUP(TRUE, getNameRow <>"",$B$3:$Z$3,"",0,-1))