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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Does col E have dates or just the year?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
=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:
Rich (BB code):
=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
 
Last edited by a moderator:
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
If column E is dates & time try
Excel Formula:
=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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Glad you've sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,194
Messages
5,623,303
Members
415,965
Latest member
Sixaside

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