Need a Direct to replace Indirect

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1From:1/1/21 12:00 AM2099
2To:12/31/21 11:59 AM
3DateProduct CodeLot NumberSample Type Time Signed InTime CompletedTotal Sample TimeExpected Time
4       
5       
6       
7       
8       
9       
10       
Fluids
Cell Formulas
RangeFormula
C1C1=(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:A10A4=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:D10B4=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:C10C4=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:E10E4=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:F10F4=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:G10G4=IFERROR((F4-E4)*1440,"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
You could try using the offset function instead
 

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
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:
Excel Formula:
=SUM(INDIRECT("Fluids!E1:E"&MATCH(30,Fluids!E:E)))
Excel Formula:
=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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,025
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

AFAIK Offset doesn't work with closed workbooks either
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,025
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not sure why you are using INDIRECT in the 1st place, try
Excel Formula:
=(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

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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)...

Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,025
Office Version
  1. 365
Platform
  1. Windows
Try removing the @ sign
 

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
No change. I'm guessing I'm not referencing the year correctly here for this purpose:

Excel Formula:
('[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...
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,130,417
Messages
5,642,003
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top