Excel Lookup and SUMIF to data on Different Workbook **Help Please**

[dwilson38550m]

Hi,

I wonder if anyone could help as I have been wracking my brains. I have set up a fomula to lookup against data on a different tab on the SAME workbook.

Example below - this lookups the tab number noted on cell D21 of the current sheet, finds the 2nd column (column B) against the lookup parameter ("Total Expected Order" or "Total Expected Office Hours") and returns the number. (on Sheet1) The tab number noted in cell D21 is on the SAME workbook.

EXAMPLE.xlsx (tab : Sheet1)
=IF(ISNA(VLOOKUP("Total Expected Order",INDIRECT($D21&"!$A:$Z"),2,FALSE)),0,VLOOKUP("Total Expected Order",INDIRECT($D21&"!$A:$Z"),2,FALSE))

=SUMIF(INDIRECT($D21&"!$A:$A"),"Total Expected Office Hours",INDIRECT($D21&"!$B:$B"))

PROBLEM :

What if I wanted to lookup or SUMIF on data in a DIFFERENT workbook & tab - how can I do this if I know the names of the workbook and tab (there will be multiple books and tabs so I need to define these in a particular cell I want the data returned to on EXAMPLE.xlsx (tab : Sheet1). Can I set cell D21 to something like C:\DATA\FILEA.xlsx[SheetA], or C:\DATA\FILEB.xlsx[SheetB] etc etc.

Thanks in advance.

David

 

[James006]

Hi,

The first question to solve is : are you working with opened ... or closed workbooks ...?

 

[dwilson38550m]

Hi, the data I am looking up (target file) is closed. Thanks.

 

[James006]

Hi,

Working with closed files does change several things ...

see : https://exceloffthegrid.com/getting-values-from-a-closed-excel-workbook/

Hope this will help

 

[dwilson38550m]

Ok I'll give that a go. Thanks. Although I am linking to hundreds of target files so not sure how successful this will be.

 

[James006]

With so many external links, you should probably take a look at Power Query

https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/

Hope this will help

 

[dwilson38550m]

Hi, thanks for the links but I am still struggling. I am trying to pick up data in another worksheet (file name noted in say cell D21). I have tried a few permutations but it still comes up with #REF . Linking direct to the target worksheet ='C:\Bank\[Test2.xlsx]Sheet1'!$A1:$B6 won't work as I have hundreds of source spreadsheets so I need to flex cell D21 accordingly.

Thanks.