Find Values in Random Cell Within A Column

[emisa]

Hi there,

Newbie here. I am tryingn to figure out a formula where I can refer a cell (containing a date) within a specific column, say in Column A, I have A7 containing a date and rows below are blank A8 and so forth, then A26 has another date, and rows below are black once again. I am hoping to combine FIND, INDEX, and MATCH but somewhere Im lost. E9 down to E25 must have the formula where it will show the date from A7, but this changes in E28 and so forth where it must show the new date. Thanks heaps for your help!!!

 

[zero269]

My first suggestion is to use the forums Excel Add-In to share your Sample Data... along with an example of what results you are expecting to achieve. You'll have to hard code those values of course.
However, I'm thinking XLOOKUP is what you're looking for...

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[emisa]

Please see table and the comment on the far right hand side of the sheet. Thanks in advance. appreciate your help.

_Report Output_CMDHB Daily Staffing Sheet PH_04002843_2024-12-29T12_36_59.390(44).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1												CMH Daily Staffing Sheet
2	Time Period :	30/12/2024 - 05/01/2025																Executed on :			29/12/2024 12:36
3	Query :	All Home																Printed for :			Raeina, Naomi
4
5
6	Shifts		Shift Start					Label			Job			Name	Start		End			Hours			Notes
7	Monday 30/12/2024
8			07:00
9					12/30/24		TIME NOT CONFIRMED				ACCN			Oster, Jessica L	07:00		14:30			7.5					* Column E must show the date from A7, A26 and so on.
10					12/30/24		C0700-1930	C0700-1930			ACNM			Farrell, Jo A	07:00		19:30			12.5					* This sheet will be a template where there will be raw data in another sheet. Hence the rows containing the date may move up or down depending on the number of staff scheduled for the that date.
11					12/30/24		C0700-1530 Admin Day	C0700-1530 Admin Day			ACNM			Gilhooly, Andrew J	07:00		15:30			8.5					* The data in Column E (the date from a refererenced Cell) may also show or not show depending on the value in Column G. Column G is deemed true if shows "C0700-1900", "C1900-0700" etc….
12					12/30/24		C0700-1530 CC	C0700-1530 CC			CC			Lester, Whitney J	07:00		15:30			8.5
13					12/30/24		C0700-1730	C0700-1730			CNS			Culverwell, Pamela D	07:00		17:30			10.5
14					12/30/24		C0700-1530	C0700-1530			CNS			Jenkins, Nicole A	07:00		15:30			8.5
15					12/30/24		C0700-1930	C0700-1930			HCA			Tateishi, Seiko	07:00		19:30			12.5
16					12/30/24		C0700-1930	C0700-1930			HCA			Kumar, Poonam S	07:00		19:30			12.5
17					12/30/24		C0700-1930	C0700-1930			HCA			Heka, Ninette S	07:00		19:30			12.5
18					12/30/24		C0700-1730	C0700-1730			NM			Devoy, Kathleen L	07:00		17:30			10.5
19					12/30/24		C0700-1930	C0700-1930			RN			Smith, Chloe J	07:00		19:30			12.5
20					12/30/24		C0700-1930	C0700-1930			RN			Brady, Kelly F	07:00		19:30			12.5
21					12/30/24		C0700-1930	C0700-1930			RN			Gibb, Lochlain J	07:00		19:30			12.5
22					12/30/24		C0700-1930	C0700-1930			RN			Hapalla, Marcelino I	07:00		19:30			12.5
23					12/30/24			C1900-0730			RN			Kuriachan, Honey	19:00		07:30			12.5
24								C1900-0730			CRN			Sarasakshan Pillai, Arun	19:00		07:30			12.5
25								C1900-0730			CRN			Altena, Paul G	19:00		07:30			12.5
26	Tuesday 31/12/2024
27			07:00
28					12/31/24		TIME NOT CONFIRMED				ACCN			Oster, Jessica L	07:00		14:30			7.5
29					12/31/24		C0700-1930	C0700-1930			ACCN			Sturrock, Christine M	07:00		19:30			12.5
30					12/31/24		C0700-1530 CC	C0700-1530 CC			CC			Davis, Rachel A	07:00		15:30			8.5
31					12/31/24		C0700-1530	C0700-1530			CNS			Jenkins, Nicole A	07:00		15:30			8.5
32					12/31/24		C0700-1730	C0700-1730			CNS			Culverwell, Pamela D	07:00		17:30			10.5
33					12/31/24		C0700-1930	C0700-1930			HCA			Kafa, Vika F	07:00		19:30			12.5
34					12/31/24		C0700-1930	C0700-1930			HCA			Uta, Ofisa C	07:00		19:30			12.5
35					12/31/24		C0700-1930	C0700-1930			HCA			Taviliniu, Evaata	07:00		19:30			12.5
36					12/31/24		C0700-1930	C0700-1930			HCA			Ruiterman, Asuncion	07:00		19:30			12.5
37					12/31/24		C0700-1730	C0700-1730			NM			Devoy, Kathleen L	07:00		17:30			10.5
38					12/31/24		C0700-1930	C0700-1930			RN			Smith, Chloe J	07:00		19:30			12.5
39					12/31/24		C0700-1930	C0700-1930			RN			Petrie, Elizabeth M	07:00		19:30			12.5
Report

Cell Formulas
Range	Formula
G9:G22,G28:G39	G9	=IF(H9="","TIME NOT CONFIRMED",H9)

 

[hagia_sofia]

Hello, if you have Excel 365, you could e.g. try in E7:

=IF(ISBLANK(A7:A39)+ISBLANK(C7:C39)<2,"",SCAN("",A7:A39,LAMBDA(a,b,IF(b<>"",b,a))))

Alternatively you could try:

=IF(OR(ISNUMBER(A7),ISNUMBER(C7)),"",IF(ISNUMBER(A5),A5,E6))

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=IF(G9="","",LOOKUP(10^9,A$7:A9))

 

[emisa]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=IF(G9="","",LOOKUP(10^9,A$7:A9))

Thank you for your help!! Code works!!!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Thank you for your help!! Code works!!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

BTW, here is an even simpler formula that I think does the same thing.

25 01 15.xlsm
	A			D	E
1
2	Time Period :
3	Query :
4
5
6	Shifts
7	30/12/2024
8
9					30/12/2024
10					30/12/2024
11					30/12/2024
12					30/12/2024
13					30/12/2024
14					30/12/2024
15					30/12/2024
16					30/12/2024
17					30/12/2024
18					30/12/2024
19					30/12/2024
20					30/12/2024
21					30/12/2024
22					30/12/2024
23					30/12/2024
24
25
26	31/12/2024
27
28					31/12/2024
29					31/12/2024
30					31/12/2024
31					31/12/2024
32					31/12/2024
33					31/12/2024
34					31/12/2024
35					31/12/2024
36					31/12/2024
37					31/12/2024
38					31/12/2024
39					31/12/2024
Dates

Cell Formulas
Range	Formula
E9:E23,E28:E39	E9	=IF(A7,A7,E8)