Linking Workbooks

[mouse88]

I am working on a project currently where I have several workbooks that managers fill out (see picture below) on a daily basis.

What I would like to do if possible is have an analysis workbook which would contain a table with the same fields as above and pull data in from all of the other workbooks.

Is there a way of getting the records from a table in one sheet in to a table in another sheet?

 

[mouse88]

Anyone?

 

[VoG]

This may help http://msdn.microsoft.com/en-us/library/cc837974.aspx

 

[excelsishya]

Hi,

First create new work book with analysis tab.

Create manually link for 1 of the managerworkbook say manager1book(tabname raw)

Then copy analysis tab to get 2nd tab

use find function

analysis (2)

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>account</td> <td>team</td> <td>name</td> <td>date</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>A2</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A2</td></tr> <tr> <td>B2</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!B2</td></tr> <tr> <td>C2</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!C2</td></tr> <tr> <td>D2</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!D2</td></tr> <tr> <td>A3</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A3</td></tr> <tr> <td>B3</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!B3</td></tr> <tr> <td>C3</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!C3</td></tr> <tr> <td>D3</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!D3</td></tr> <tr> <td>A4</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A4</td></tr> <tr> <td>B4</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!B4</td></tr> <tr> <td>C4</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!C4</td></tr> <tr> <td>D4</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!D4</td></tr> <tr> <td>A5</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A5</td></tr> <tr> <td>B5</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!B5</td></tr> <tr> <td>C5</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!C5</td></tr> <tr> <td>D5</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!D5</td></tr> <tr> <td>A6</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A6</td></tr> <tr> <td>B6</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!B6</td></tr> <tr> <td>C6</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!C6</td></tr> <tr> <td>D6</td> <td>='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!D6</td></tr></tbody></table></td></tr></tbody></table>This is link formula
='C:\Documents and Settings\P4\Desktop\[MANAGER1 BOOK.xlsx]RAW'!A2

put in find above value

replace with

'C:\Documents and Settings\P4\Desktop\[MANAGER2 BOOK.xlsx]RAW'

click replace all it automatically work

similarly repeat same procedure.

after all is done u can keep book dynamic or break links.(data edit links break)

 

[mouse88]

This will be too much work as there will be a seperate worksheet for each day of the year and seperate for about 20 different managers.

I wanted to just have records pulled from the managers sheets into the table in the main sheet and have it automatically update.

 

[excelsishya]

Hi mouse88,

May be i needed to be much clearer,steps i suggested may sound time consuming but it not that much.

When i told "Create manually link for 1 of the managerworkbook say manager1book(tabname raw)" i mean just 1 relative cell link as you wanted have same structure.

Then you can create 20 tabs copy(right click create copy of tabs 20 times)

Use find and replace change link in each of 20 tab has i suggested previously.

This is only 1 time.


after that each day you need to change just date(assuming your managers save file same name each day with just date change eg manager105062011.xlsx) use find & replace with work book option selected.

Initially i though u wanted in analysis work book. if you want it in single table run pivot on multiple tabs(20) create structure of your liking.

each new day just change date and refresh pivot.

1st time it may take 20 min . each new day it will be less than min work.