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

dwilson38550m

Board Regular
Joined
Nov 21, 2005
Messages
89
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,

The first question to solve is : are you working with opened ... or closed workbooks ...?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top