A linking question

kluitna

Board Regular
Joined
Mar 10, 2002
Messages
75
I hope someone out there can help me. What I have is several workbooks that have lookup up lists in them. The list is the same for all the workbooks. What I would like to do is have just one master list, so when the list needs to be edited it only has to be edited once not multiple times for every given workbook. I have been able to get the look up lists to work when its in a seperate file, but occationally I need to copy data from the list to a given worksheet or workbook. I have been able to make this work inside a given workbook, but I can't get the code to work for copying from another workbook. I hope I am making since here. The problem I am having is opening and closing the copy source and the getting the it to select the destination. It seems very simple, but I can manage to do one or the other not both.

Any help is much appreciated.

Paul
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Someone else probably will have a cleaner solution, but what you may have to do (if the scope of the table isn't too large) is on a new sheet in the destination worksheet, link to the actual table....ie....=+[table.xls]Sheet1!A1 (where table.xls is the spreadsheet with the data) and then copy that formula over to bring in the rest of the data. then vlookup to the "new" data you just brought in. That way you only have to edit the one table. However, if the data is dynamic and # of rows is always changing, this may need modification.
 
Upvote 0
Wi_guy,

that sounds like a very interesting solution. did not even knwo that was possible. I will give it a try

Thanks
 
Upvote 0
wi_guy, tried your idea, not a bad one at all and will work for my purposes, but when I typed put the formula in the cell it brought the formating across but only the data in the cell A1, I tried to tell excel to look at more of the sheet by expandind the range but that did not work. Anyone have any idea how to make this work?
 
Upvote 0
you lost me on the question there.......one thing to make sure of, is that you change the reference from absolute to relative before copying......when you link to a different sheet, it throws the $A$1 in and needs to be changed to A1 before copying

can you give a better example of the latest problem please?
 
Upvote 0
wi_guy,

I figured out what I did wrong misunderstood what you ment. Your idea will work fine going to implement it into some code now. Thanks fot the help. Should simplify my workbooks allot
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top