INDEX Formula that Indexes cells with specific text String

[Monica at Plastics]

I have a situation similar to this old thread here: https://www.mrexcel.com/forum/excel...-only-indexes-cells-specific-text-string.html

WE:	1/18/2019					46:00
	Day	Start	End	Lunch	Total
1/14	Monday	05:00	14:30	00:00	09:30
1/15	Tuesday	05:00	14:30	00:00	09:30
1/16	Wednesday	05:00	14:15	00:00	09:15
1/17	Thursday	06:00	14:30	00:30	08:00
1/18	Friday	06:00	11:30	00:00	05:30
1/13	Saturday	06:15	10:30	00:00	04:15
WE:	1/25/2019					18:15
	Day	Start	End	Lunch	Total
1/21	Monday	05:37	07:29	00:00	02:00		PTO	08:00	8
1/22	Tuesday	00:00	00:00	00:00	00:00		PTO	08:00	8
1/23	Wednesday	00:00	00:00	00:00	00:00		PTO	08:00	8
1/24	Thursday	05:50	14:30	00:30	08:15
1/25	Friday	05:51	13:54	00:00	08:00
1/19	Saturday

<tbody>
</tbody>

I want to use index but when it finds the word PTO I want it to return the date.

 

[James006]

Hello,

Sadly your message is perfectly incomprehensible ...

Try to clarify it ...with on one hand what you currently have

and on the other hand your expected result ...

 

[sadboy309]

I need your file Excel, you post link update on Drive.google.com or Onedrive.live.com. Now.

 

[Monica at Plastics]

So what I want is for it to return each employee's PTO amounts, without skipping rows on the results tab. I need it to show the date that it was taken and in a different column the hours that were taken on that date.

 

[AhoyNC]

Maybe something like this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	WE:	1/18/2019					46:00:00
2		Day	Start	End	Lunch	Total
3	1/14	Monday	5:00	14:30	0:00	9:30
4	1/15	Tuesday	5:00	14:30	0:00	9:30
5	1/16	Wednesday	5:00	14:15	0:00	9:15
6	1/17	Thursday	6:00	14:30	0:30	8:00
7	1/18	Friday	6:00	11:30	0:00	5:30
8	1/13	Saturday	6:15	10:30	0:00	4:15
9
10
11	WE:	1/25/2019					18:15
12		Day	Start	End	Lunch	Total
13	1/21	Monday	5:37	7:29	0:00	2:00		PTO	8:00	8
14	1/22	Tuesday	0:00	0:00	0:00	0:00		PTO	8:00	8
15	1/23	Wednesday	0:00	0:00	0:00	0:00		PTO	8:00	8
16	1/24	Thursday	5:50	14:30	0:30	8:15
17	1/25	Friday	5:51	13:54	0:00	8:00
18	1/19	Saturday
19
20
21	PTO
22	Date	Total
23	1/21	2:00
24	1/22	0:00
25	1/23	0:00
Sheet

 

[Monica at Plastics]

The only thing is I only want it to return only the PTO dates, I do have Holiday in that same column H as the PTO.

 

[Monica at Plastics]

is it possible to index this formula? =IF(Calton_Jacob!$H108='PTO Taken'!$A$1,Calton_Jacob!$I108,"")

 

[AhoyNC]

It would help if you could post a small sample that is representative of your data and the expected results.
What does Holiday look in column H? The formula I posted would only pickup PTO in that column.
What data is in sheet Calton_Jacob?
What is in cell A1 of 'PTO Taken'?

 

[Monica at Plastics]

The same data that I sent you previously is on each tab I just have a tab for each employee. Holiday is just spelled out in column H like the PTO was. cell A1 of 'PTO taken' has the word PTO. so it always points back to it. If the formula will only pickup up the PTO then that is what I was wanting. the formula worked I just will need it to be dynamic. I included more rows.

 

[AhoyNC]

One way would be just to increase the range. If your data would not go past row 1000 then set the range to something like A1:A1000 as in example below. If you want to pull from different sheets then something like the INDIRECT function should work. See this link:
https://www.youtube.com/watch?v=cjBEBn6YMi0

Excel Workbook
	A	B
1	PTO
2	Date	Total
3	21-Jan	2:00
4	22-Jan	0:00
5	23-Jan	0:00
PTO

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	WE:	1/18/2019					46:00:00
2		Day	Start	End	Lunch	Total
3	14-Jan	Monday	5:00	14:30	0:00	9:30
4	15-Jan	Tuesday	5:00	14:30	0:00	9:30
5	16-Jan	Wednesday	5:00	14:15	0:00	9:15
6	17-Jan	Thursday	6:00	14:30	0:30	8:00		Holiday
7	18-Jan	Friday	6:00	11:30	0:00	5:30
8	13-Jan	Saturday	6:15	10:30	0:00	4:15
9
10
11	WE:	1/25/2019					18:15
12		Day	Start	End	Lunch	Total
13	21-Jan	Monday	5:37	7:29	0:00	2:00		PTO	8:00	8
14	22-Jan	Tuesday	0:00	0:00	0:00	0:00		PTO	8:00	8
15	23-Jan	Wednesday	0:00	0:00	0:00	0:00		PTO	8:00	8
16	24-Jan	Thursday	5:50	14:30	0:30	8:15		Holiday
17	25-Jan	Friday	5:51	13:54	0:00	8:00
18	19-Jan	Saturday
19
Calton_Jacob