Linking Data in different Workbooks (Excell 2000/SR-1)

Topic

New Reply

I searched the archives, but don’t see this exact issue.

I have 6 different workbooks (for 7 different users). These work books with a sheet for each month, track various data that the user manually enters. Each sheet totals at the bottom and links to a “Yearly total” sheet.

I’ve a macro that finds the last row used, minus the total blocks and adds lines to the worksheet as they need to add data (as the data varies from month to month).

–Everything works well to right about here…

There is an eigth workbook built the same way to this point, but also has a montly total page. Each of the other workbooks LINK those page (monthly) totals to the 8th workbook.

At this point, we’ll say the first total cell is at A14. When the user “Adds line” this will move the total cell down to A15 and so on. Some sheets have 100+ lines so the total cells might end up around A122.

When I tested, I did the page total “Paste LInk”. Even though I did the paste link when the cell was A14, it found the total cell(s) correctly after additional lines where added. I tested this by adding lines/saving/closing/reopening/etc.

However, when I turn it over to the users, the links appear to freeze somewhere and never find all the line additions. As soon as I go back and re-link them, they act fine again, until the users use them again…

Any ideas why the links don’t update?

Reply | Quote

Replies

I am not quite sure of this, I haven’t test it out, but maybe you can force the links to be updated, using VBA

Sub test()
Dim NameLink
NameLink = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(NameLink) Then
   MsgBox NameLink(1)
End If
ActiveWorkbook.UpdateLink Name:=NameLink, Type:=xlLinkTypeExcelLinks
End Sub

I think your workbooks have to be saved before this will work. I just added the msgbox line of code for testing, you can leave that out, once it works.

Reply | Quote

Shot in the dark…but is “Update Remote References” checked?

Also, when I paste a link from one workbook to another, the link is an absolute cell reference, instead of a relative cell reference. I don’t know of any way to make the default way of pasting a link as a relative reference. You might try editing the first link in the formula bar from absolute to relative, and then copying that cell to wherever you want the next link. Trouble is, sometimes this works well and sometimes not, depending on the layout of one workbook vs another.

Reply | Quote

I’m a rookie on VB, guys.
What will the above macro do?
Where does it go; in the Parent (reporting to) document or all of the children (reporting from) or all of them?

I’m not recalling where in Excel to check the “Update Remote References”.

(Thanks for your help thus far!) Playing with it this morning, NOTHING is updating. I love MS!

Reply | Quote

Try to put the code in the macro module of the parent workbook. In your first post you mentioned to have a macro already doing a few things. Maybe you can add a few lines of code to this macro. I must admit that I don’t have much experience with links, but I tried the code I posted and it worked fine for my small simulated problem.

Reply | Quote

“Update Remote References” is under Tools/Options/Calculation/”Workbook Options” in Excel 2000.

Reply | Quote

Why not put the total line on top of the worksheet, in cell B1 perhaps. That way the location for the links remains the same all the time.

If you don’t want to see the total line, change the font color to the cell’s back color.

The user can just add data to the bottom of the sheet.
Since you already have a macro to find the last row, you can have it also update the total formula(s) on row 1 to include the last row.

Sub Update_Totals()
Dim myLastRow As String

GotoLastCell ‘macro to find last row

myLastRow = CStr(ActiveCell.Row)
Range(“B1”) = “=sum(B3:B” & myLastRow & “)”
End Sub

Reply | Quote

Thinking upsidedown. Ken, This may be the simplest idea; Totals at the top! Sometime the forest is hiding in the trees!

Thanks to you both!

Reply | Quote

A few years ago, we needed a time tracking program for 12 staff. I used the same method as Ken suggested. On each employee’s spreadsheet, I placed a sum function in the first row (A1-L1), entered a title for each column in the second row (A2-L2) and set the sum range for the columns (starting a A3-A8000 to L3-L8000). Then, I freeze the first two rows (Windows menu|Freeze Panes). (The macro Ken has will work, but, if the employees always save their spreadsheet where they last entered data, the spreadsheet will open to that location. But, it would probably be better to have the macro.)

In the supervisor’s spreadsheet, I listed all of the employees in column A and for each employee linked to their totals row (A1-L1) to the supervisor’s column B-M. So, every time the supervisor opened his speadsheet, his information was automatically updated. It worked real well.

Then, I created pie charts for each employee that showed how they spent their time. The pie charts were updated automatically when the supervisor’s spreadsheet opened.

Of course, all of the spreadsheets were located on a shared drive.

Reply | Quote