Linking to another spreadsheet only works when other file is open

[Airbisk]

Hi,

I have a spreadsheet that as a few hundred links pulling data in from a few other spreadsheets. Some of the links work without the linked spreadsheets being opened, these have links like...

='T:\Torrent\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]PAYROLL SUMMARIES'!$B$6

='T:\Torrent\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]PLANNER'!$J$3

Other parts of this spreadsheet contains links like...

=SUMIF('T:\Torrent\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]ON CALL'!$I$4,$B147,'T:\Torrent\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]ON CALL'!$I$7)

The links above require the spreadsheet to be opened for the data to be visible otherwise a #VALUE! appears.

Is there anyway of getting the link to pull the data through without having to open the spreadsheet containing the data?

Any help would be appreciated.

Thanks

 

[xlbo]

You could go to the Edit>Links section and choose "update links"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Airbisk]

Thanks Geoff,

I have used the Edit Links window to update the links but still #VALUE! are being shown.

Would it be anything to do with SUMIF?

Regards

 

[xlhelp]

You may need to change to UNC path [\\fileserver\share]

Canadian eh! Check out the new social forum Tek-Tips in Canada.

 

[Airbisk]

Thanks xlhelp,

Just working through the links, will take a little time, will let you know the outcome shortly.

Regards

 

[Airbisk]

Sorry it's taken so long to reply back. Left my machine running the changes over the weekend...120,000 links changed.

The code in the cells is or similar to...

=SUMIF('\\vpfp3\Websites\current\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]ON CALL'!$C$4,$B2,'\\vpfp3\Websites\current\qpulse\Weekly Roster Planners\Bolton\[Week 42 Roster.xls]ON CALL'!$C$7)

The cells still appear with #VALUE! unfortunely :-(

 

[xlbo]

hmmm - did a bit of searching and I don;t think that SUMIf will work on a closed workbook unfortunately

unless anyone else knows differently.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question