Link formula to a different workbook

[Lewster]

Hi all, I would like to extract data from one workbook into another to be able to summerise the data, the formula I have below works when in the same workbook but I need help to get the data from 1 workbook in to another.

I know that the sheet 1 is where it will change:

Here is the location of the file to extract the data from: C:\Users\Richard\Downloads\Excel\Examples

and here is the name of the workbook to extract the data from:Number of alarms and finds by plant.xlsx

Formula that works in the same workbook: =SUMIF(Sheet1!$C$3:$C$15,$F3,Sheet1!D$3:D$15)

Thank you for your help

Lewster

[Lewster]

Edited post

[David A Coop]

This should work:
SUMIF('C:\Users\Richard\Downloads\Excel\Examples\[Number of alarms and finds by plant.xlsx]Sheet1!$C$3:$C$15,$F3,'C:\Users\Richard\Downloads\Excel\Examples\[Number of alarms and finds by plant.xlsx]Sheet1!D$3:D$15)

but it makes for a very long formula. It won't look so big if the file is open!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Lewster]

Yes it is very long and the formula above did not work but... what you said about the sheet being open made me think, I then just used = to copy a cell from the workbook I want the data from and then looked at and used part of the formula it gave me.

I then inserted part of the formula I already had for the calculation and it gave me an error on the F3 part of the formula as it was using F3 from the sheet I was using rather than F3 in the workbook I wanted to extract the data from, so adjusted again and below is the formula that works.

Thank you for your help and for getting my brain thinking.
Lewster

SUMIF('[Number of alarms and finds by plant.xlsx]Sheet1'!$C$3:$C$15,'[Number of alarms and finds by plant.xlsx]Sheet1'!$F3,'[Number of alarms and finds by plant.xlsx]Sheet1'!D$3:D$15)

[Tony Valko]

SUMIF requires that the source file be open. Use SUMPRODUCT instead. The source file does not need to be open.

=SUMPRODUCT(--('[Number of alarms and finds by plant.xlsx]Sheet1'!$C$3:$C$15='[Number of alarms and finds by plant.xlsx]Sheet1'!$F3),'[Number of alarms and finds by plant.xlsx]Sheet1'!D$3:D$15)

[Lewster]

SUMIF requires that the source file be open. Use SUMPRODUCT instead. The source file does not need to be open.

=SUMPRODUCT(--('[Number of alarms and finds by plant.xlsx]Sheet1'!$C$3:$C$15='[Number of alarms and finds by plant.xlsx]Sheet1'!$F3),'[Number of alarms and finds by plant.xlsx]Sheet1'!D$3:D$15)

Awesome, Thanks Tony.

[Tony Valko]

You're welcome!