Solved Linking multiple workbooks to one workbook

December 4, 2012 at 12:04:57
Specs: Windows XP

I have five team workbooks, each set up with up to 15 employee names in colum A, employee tasks set up on row 6, c6-m6, with dates as the worksheet name (Nov 26, Nov, 27) etc. The workbooks are title with the team name and a two week date and there are ten dated worksheets per workbook.

I need to take every employee name with all of the entries and move it into another workbook so tasks can be compared for each employee. The new workbook will also be set up with ten worksheets, named by the date.

The data will be changing so it has to be updated on the combined workbook every time I open it. Can someone please help?



See More: Linking multiple workbooks to one workbook

Report •


#1
December 4, 2012 at 12:17:04

Please do not post multiple questions on the same subject.

When and if someone can help you, they will respond. Your task is not a simple one, and since we are all volunteers at this site, you'll have to be patiernt and wait for a response.

I have deleted the original question that you posted less than 24 hours ago.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 4, 2012 at 12:23:13

Wait, DerbyDad, I have the same problem but was looking for two different solutions: the first time, I asked about scripting and when no one said anything, I took that to mean that no one with any VBA experience could help.

Then I asked about Linking, which, if I understand it correctly, is an Excel command, something that requires no scripting, just selecting cells from different work sheets? Isn't that correct?


Report •

#3
December 4, 2012 at 16:18:10
✔ Best Answer

re: "when no one said anything, I took that to mean that no one with any VBA experience could help."

It probably just means that no one with VBA experience had the time right then and there to respond. We're all just volunteers here and dedicating a large amount of time to a question only happens when someone has the time.

Linking is really nothing more than a formula:

- Links within a sheet:

In B1 put =A1

- Links between sheets:

In Sheet1!B1 put =SHEET2!A1

- Links between workbooks:

In '[Book1.xlsx]Sheet1'!$B$1 put ='[Book2.xlsx]Sheet1'!$A$1

If Book2.xlsx is open, you'll see ='[Book2.xlsx]Sheet1'!$A$1 in the cell.

If Book2 is closed, you'll see something like:

='C:\Documents and Settings\DerbyDad03\My Documents\[Book2.xlsx]Sheet1'!$A$1

In other words, Excel will add the complete path to the file.

- You can even use functions across linked workbooks:

e.g. in Book2, you could use this to VLOOKUP something in Book3:

=VLOOKUP(A1,[Book3.xlsx]Sheet1!$A$1:$B$5,2,0)

If you close Book3, Excel will add the path to the file within the function.

- You don't have to type the "link", you can type the equal sign and then click in the cell on the other sheet or in the other workbook and Excel will put the correct syntax in the cell.

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 5, 2012 at 05:49:05

Yes, that was helpful, DerbyDad, thanks! And clicking between workbooks was easier than typing it in.

Do you know of anyway you can do that with multiple cells? Like if I wanted column A, A2-A15 to move to another workbook, B2-B15, I tried selecting the cells, hitting = then selected the cells with the data and only the first cell populated.

Did I miss a step?


Report •

#5
December 5, 2012 at 08:22:02

1 - Select B2:B15
2 - Press =
3 - Select A2 in the other workbook
4 - If the formula includes $ in the cell reference, press F4 until they are all gone
4 - Hit Ctrl-Enter

Ctrl-Enter auto fills the selected range with whatever is entered in the first cell, incrementing each formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 5, 2012 at 08:27:01

That is PERFECT! Thank you so much!

Report •


Ask Question