A linking question

kluitna · Apr 2nd, 2002, 01:18 PM

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

wi_guy · Apr 2nd, 2002, 01:29 PM

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.

kluitna · Apr 2nd, 2002, 01:32 PM

Wi_guy,

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

Thanks

kluitna · Apr 2nd, 2002, 01:48 PM

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?

wi_guy · Apr 2nd, 2002, 01:52 PM

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?

kluitna · Apr 2nd, 2002, 01:55 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 01:18 PM	#1
kluitna Board Regular Join Date: Mar 2002 Posts: 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

Apr 2nd, 2002, 01:29 PM	#2
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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.

Apr 2nd, 2002, 01:32 PM	#3
kluitna Board Regular Join Date: Mar 2002 Posts: 75	Wi_guy, that sounds like a very interesting solution. did not even knwo that was possible. I will give it a try Thanks

Apr 2nd, 2002, 01:48 PM	#4
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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?

Apr 2nd, 2002, 01:52 PM	#5
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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?

Apr 2nd, 2002, 01:55 PM	#6
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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