Excel links

[Guest]

I have identical files in separate workbooks (for different and unique
purposes). Each workbook consists of 19 worksheets (one worksheet for each of
our centres). Each tab holds the employees, their position, employment
status, hours per day and days per week for that centre.

I want to link these two workbooks so that changes to staffing reflect in
appropriate worksheet in both workbooks. When I perform the typical link it
only works in the one cell. I cannot copy and paste. It would be a huge
undertaking to have to link each individual cell in these 19 worksheets and
more than 200 employees.

ANY HINTS?

 

[Richard O. Neville]

Two ideas: (1) have you considered merging the two workbooks into one? This
would make the cross-references internal rather than external; when you
create the first one, say, in cell B2, you can then drag down and across to
create references for all the other cells.

(2) If you keep the workbooks separate, you could change the absolute
references to relative by eliminating the $ signs. For instance, change
=[external reference]$B$2 to =[external reference]B2. You will then be able
to drag down and across.