Links

[Matt_Chelmsford]

Hello there,

I am struggling with my links Spreadsheet A's data feeds into Spreadsheet B and Unless I have the pair of them open at the same time I end up with a load of #values. Is there anyway you can update the receiving spreadsheet whilst the giving spreadsheet remains closed?

I will be grateful for any help given!

Regards

Matthew

 

[Andrew Poulsom]

Please give an example of a formula that returns #VALUE! if the source workbook is closed. Note that the INDIRECT function expects the source workbook to be open.

 

[Matt_Chelmsford]

The Forumala I am using is =SUMIF('[Transfers Control.xls]Transfers'!$D$3:$D$5000,A87,INDEX('[Transfers Control.xls]Transfers'!$E$3:$O$5000,0,MATCH($E$3,'[Transfers Control.xls]Transfers'!$E$2:$O$2)))

And the sheet I am looking up does have an indirect formula - is this the problem but this is in column C and I am looking up from column D!

 

[Andrew Poulsom]

You can use SUMPRODUCT with a closed workbook:

=SUMPRODUCT(--('[Transfers Control.xls]Transfers'!$D$3:$D$5000=A87),INDEX('[Transfers Control.xls]Transfers'!$E$3:$O$5000,0,MATCH($E$3,'[Transfers Control.xls]Transfers'!$E$2:$O$2)))