Need a Direct to replace Indirect

[rtroehrig]

So I use this combination a lot and it generally always works perfectly, but typically the source data is in the same workbook. This enables me to pull all the data I need from one sheet to another by date/time range. This time I need it to pull from a different source workbook without having to open the source workbook, so this clearly will not work because it contains an INDIRECT function. I can't seem to wrap my head around converting this to a direct function so that it will operate the way I want it to. Anybody have any ideas?

QC Time Study.xlsx
	A	B	C	D	E	F	G	H
1	From:	1/1/21 12:00 AM	2099
2	To:	12/31/21 11:59 AM
3	Date	Product Code	Lot Number	Sample Type	Time Signed In	Time Completed	Total Sample Time	Expected Time
4
5
6
7
8
9
10
Fluids

Cell Formulas
Range	Formula
C1	C1	=(COUNT('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E)+2)-COUNT(IF(((INDIRECT("'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E2:E"&MATCH(10^308,'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E)))>=(B1))*((INDIRECT("'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E2:E"&MATCH(10^308,'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E)))<=(B2)),INDIRECT("'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!Q2:Q"&MATCH(10^308,'https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E))))
A4:A10	A4	=IF(INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E,$C$1+ROWS(A$4:$A4))=0,"",INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E,$C$1+ROWS(A$4:$A4)))
B4:B10,D4:D10	B4	=IF(INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!B:B,$C$1+ROWS($A$4:B4))=0,"",INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!B:B,$C$1+ROWS($A$4:B4)))
C4:C10	C4	=IF(INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!A:A,$C$1+ROWS($A$4:C4))=0,"",INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!A:A,$C$1+ROWS($A$4:C4)))
E4:E10	E4	=IF(INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!F:F,$C$1+ROWS($A$4:E4))=0,"",INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!F:F,$C$1+ROWS($A$4:E4)))
F4:F10	F4	=IF(INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!Q:Q,$C$1+ROWS($A$4:F4))=0,"",INDEX('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!Q:Q,$C$1+ROWS($A$4:F4)))
G4:G10	G4	=IFERROR((F4-E4)*1440,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Does col E have dates or just the year?

 

[rtroehrig]

Column E is stamping =NOW() when data is entered in another Column. I have it formatted to show mm/dd/yyyy.

What do you think about replacing:

=IFERROR(INDEX('[QC Log.xlsm]Fluids'!$E:$E,AGGREGATE(15,6,ROW('[QC Log.xlsm]Fluids'!$E$3:$E$3000)/('[QC Log.xlsm]Fluids'!$E$3:$E$3000=YEAR($C$1)),ROWS(A$4:A6))),"")

With:

=IFERROR(INDEX('[QC Log.xlsm]Fluids'!$E:$E,AGGREGATE(15,6,ROW('[QC Log.xlsm]Fluids'!$E$3:$E$3000)/(('[QC Log.xlsm]Fluids'!$E$3:$E$3000>=(B1))*('[QC Log.xlsm]Fluids'!$E$3:$E$3000<=(B2))),ROWS(A$4:A6))),"")

B1 is 1/1/21 12:00 AM
B2 is 12/31/2021 11:59 PM

 

[Fluff]

If column E is dates & time try

=IFERROR(INDEX('[QC Log.xlsm]Fluids'!$E:$E,AGGREGATE(15,6,ROW('[QC Log.xlsm]Fluids'!$E$3:$E$3000)/YEAR(('[QC Log.xlsm]Fluids'!$E$3:$E$3000)=YEAR($C$1)),ROWS(A$4:A6))),"")

 

[rtroehrig]

That's giving me a Spill.

It would be perfect to be able to just enter the year in one cell and pull, but the last one I posted seems to be doing the job so I'll just enter the beginning and end and let it ride. I just need to switch out the E3000 with the match 10^308 and I should be in the clear.

Appreciate the guidance and being a sounding board as I worked through this!

 

[Fluff]

Glad you've sorted it & thanks for the feedback.