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.

 

[offthelip]

You could try using the offset function instead

 

[rtroehrig]

I won't lie, I'm not familiar with offset. I wouldn't know how to put that together to do what this is doing.

Maybe I'm overthinking it altogether and it doesn't need to be as complicated as I've built this. I basically need it to go from one sheet in one workbook to another sheet in another workbook if the date falls within 2021 and then pack at the top. I just need it to do what this won't do, which is operate without opening the source workbook.

 

[offthelip]

It is worht learning about offset it is much more useful than indirect I think:
These two equations do exactly the same thing which is sum rows 1 to where 30 is found on column E on th fluid sheet:

=SUM(INDIRECT("Fluids!E1:E"&MATCH(30,Fluids!E:E)))

=SUM(OFFSET(Fluids!E1,0,0,MATCH(30,Fluids!E:E)))

the point about using offset is that the addres to your other workbook is permanently in the equation rather than "calculated" and so it is in the excel descision tree when it comes to a recalculation so I would expect it to work just like any direct reference to the other workbook, and not require it to be open. But I haven't tested this!!
It is also much easier to debug errors with offset than it is with indirect

 

[Fluff]

AFAIK Offset doesn't work with closed workbooks either

 

[offthelip]

Thanks Fluff, useful to know

 

[Fluff]

Not sure why you are using INDIRECT in the 1st place, try

=(COUNT('https://lubrizol.sharepoint.com/teams/RocktonQC/Shared Documents/General/[QC Log.xlsm]Fluids'!E:E)+2)-COUNT(IF((('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))*(('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)),'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)))

I think I 've got it right, but obviously can't test.

 

[rtroehrig]

I feel like this really wants to work, but I'm only getting 2 lines back.

So there's another sheet I have that is pulling based on cell contents. I think you may have even helped me on this one Fluff. I'm thinking maybe there is a way I can modify this one to pull based on Year, but how I have it written here is just returning blanks. This is just for one cell, so I'll need to modify for each column later (and probably remember how to throw in the infinite column read)...

=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$2:A4))),"")

 

[Fluff]

Try removing the @ sign

 

[rtroehrig]

No change. I'm guessing I'm not referencing the year correctly here for this purpose:

('[QC Log.xlsm]Fluids'!$E$3:$E$3000=YEAR($C$1))

You would think this would be a lot easier to simply pull things to another workbook if the date exists within the year...