Linking makes sheet disappear

[RaviP]

I used to link certain cells in various xls files to pick up data from another xls file. Each of these files were quite large and would become very cumbersome to handle if merged into a single file. The orginating xls file consisted of a number of a worksheets of which only one sheet had cells which had to be linked outside this file. This file itself had some other cells linked to other cells in another sheet.
Normally it would all work fine. I could keep going to the originating file and update data onto that, and the linked file too did not give me any trouble
But recently at times the originating file would not open the sheet which had the linked cells. I used to think that perhaps the sheet got hidden. Then I realised that the problem apparently is due to some issue with Visual Basic. I would have to then go to the other files and remove the linking to this file. Only then would that sheet open
Yesterday I was in a state of panic. My technique simply would not work! Also the file properties showed that the file was last modified more than a year ago, although I had accessed and updated the file less than a month ago
So I did a file search for all xls files modified in the past 2 months. My file now showed up with the correct properties and I opened it to find all the sheets intact
Can anybody explain what is at the bottom of the problem? Could it be a problem with the O/s or Office/Excel
My PC is an old Celeron running W2K Professional Version 5.0.2195 Service Pack 4 build 2195. I use updated Office 2000, Norton AntiVirus 2004 and ZoneAlarm firewall.

 

[Earl Kiosterud]

RaviP,

This is a bit unclear. Linking doesn't require opening the workbook to
which the link refers, though certain references to other
workbooks(INDIRECT, for one) require the workbook be open (though it doesn't
attempt to open it).

Be clear about the linking. It isn't a two-way street. One cell contains
the data, the other contains the link (=workbook.xls Sheet1 A1). Be clear
describing to us which is which.

You say putting all the data in a single workbook would be cumbersome. I've
always found it substantially less cumbersome in the long run to keep
related data in a single workbook (and in a single sheet, where at all
applicable) -- even if it makes for a large workbook. Splitting related
data into separate sheets or into separate workbooks should be done only
when there's a clear requirement to do so (for example, security -- you
don't want some of the data visible to certain users).
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I used to link certain cells in various xls files to pick up data from

another xls file. Each of these files were quite large and would become very
cumbersome to handle if merged into a single file. The orginating xls file
consisted of a number of a worksheets of which only one sheet had cells
which had to be linked outside this file. This file itself had some other
cells linked to other cells in another sheet.

Normally it would all work fine. I could keep going to the originating

file and update data onto that, and the linked file too did not give me any
trouble.

But recently at times the originating file would not open the sheet which

had the linked cells. I used to think that perhaps the sheet got hidden.
Then I realised that the problem apparently is due to some issue with Visual
Basic. I would have to then go to the other files and remove the linking to
this file. Only then would that sheet open.

Yesterday I was in a state of panic. My technique simply would not work!

Also the file properties showed that the file was last modified more than a
year ago, although I had accessed and updated the file less than a month
ago.

So I did a file search for all xls files modified in the past 2 months.

My file now showed up with the correct properties and I opened it to find
all the sheets intact.

Can anybody explain what is at the bottom of the problem? Could it be a

problem with the O/s or Office/Excel?

My PC is an old Celeron running W2K Professional Version 5.0.2195 Service

Pack 4 build 2195. I use updated Office 2000, Norton AntiVirus 2004 and
ZoneAlarm firewall.