How to link data from different worksheets in different work

[java11]

I have 15 departments entering daily data in 15 different workbooks. Each workbook contains worksheets representing each week. As they enter the next week, they create a new worksheet with the subsequent week number.

For example, Dept A has a worksbook that has 41 worksheets representing weeks of the year to date. Next week they will create another worksheet called 42.

I have one linked administrative worksheet that takes the totals from each worksheet and places the values in cells correspnding to the name of each department.

My question is, is there a easy way so that I did not have to update the links on the administrative worksheet at the beginning of each week? I don't have to keep the archived copies of the administrative worksheet. I just need to show the totals for each department for the current week only.

Thanks for any help.

Bill

 

[DonkeyOte]

Not too sure I know what you're asking Bill - are you saying that you don't want to "update links" with the dept files?

Are you using the indirect function? (i.e. setting the week number in say cell a1 of your admin. file)

Care to shed some more light on this for us?

Cheers!

 

[java11]

I'm sorry for not explaining fully. I'm not that familiar with the "update link" function or the "indirect" function.

Basically what I need to do is to update a weekly admin workbook with data from 15 departmental weekly updates. Each department creates new worksheets for each new week.

If I create the admin workbook with links from the 15 departmental workbooks, the link for one cell, as an example, will display "='F:Cost Center1 Spas[spas.xls]Week 41'!$D$5".

I would like the admin sheet to update the next week automatically so that I do not have to go through each department listed in the admin workbook and update the reference from "Week 41" to "Week 42"

I hope that makes sense. Thanks!

Bill

 

[DonkeyOte]

OK this is where the indirect function becomes VERY handy...

say I want cell C1 to equal what ever the cell reference in A1 is...

e.g.

A1 = Sheet2!a10

If C1 is one of a large number of formulas that are referencing Sheet2!a10 I don't want to have to re-edit the formula for each cell if say next week I want to look at Sheet2!b33 instead of A10. So I use INDIRECT:

C1 = INDIRECT(A1)

i..e C1 = Sheet2!a10
or if I change A1 to Sheet2!b33
C1 = Sheet2!b33

In your case you want to have the reference to the week as your A1 and pick this up in your formula.


"=INDIRECT("'F:Cost Center1 Spas[spas.xls]"&A1&"'!$D$5".

where A1 = Week 41 etc...

 

[java11]

Thanks for the info! I think you saved me from hours of tedious work.

I revised the value of the linked cell as you suggested and received an error "#REF".

I entered the value of the cell as "=INDIRECT("'F:cost centerparts[parts.xls]"&J6&"!$G$11")" On the same worksheet in cell J6, I entered "week # 41" without the double quotes.

I know it's probably something really simple that I'm missing.

Thanks agian for the help!

Bill

 

[Alriemer]

Bill,

I think that INDIRECT between two files only works if both files are open. This can be frustrating if you for example need to send someone the summary but not the backup. One approach: Create your output file, pulling data from the 15 workbooks with INDIRECT (let's say "output.xls"). Then create an identical page in a separate file and line it directly to output.xls - this is a pain but not too bad using Paste Special> Format and Column Width. Call this new file "final.xls". Then it's matter of timing: open and calculate all 17 books. Then save and close final.xls. Then close all the others. When you reopen final.xls, choose not to update linked data. Then you can use the data without having all 15 source files open.

Hope this helps.
Alriemer

 

[java11]

Al,

Quick question, when I reference the "A1" in my formula in the admin workbook, is it the A1 in the admin workbook or is it the A1 in on eof the department workbooks?

Bill

 

[Alriemer]

Bill,

I'll try to keep this clear. Suppose you want to populate the range B2:B16 with data located in cell C3 of Sheet1 of 15 different books. First enter "Sheet1!C3" (without the double quotes) in B1. Then enter "cath[book1.xls]", "cath[book2.xls]", "cath[book3.xls]" etc. in A2:A16.
Finally, enter =INDIRECT($A2&$B$1) in B2 and copy through B2:B16.

To answer your question, you are referring to A1 and B1 in the "home" sheet, but those cells contain address information (as text strings) about data elsewhere.

INDIRECT will accept any method you use to build up the adresses as text stings. For example, ="book"&text(row()-1),"0")&".xls"

To make sure you have the correct syntax for your synthetic addresses, just make a direct link to the cell you are referring to. One pitfall is needing to include single quotes (') around workbook and sheet names when the sheet name includes spaces:
Cath'[book1.xls]First Sheet'!C3

Hope this helps.
Alriemer

 

[java11]

Alreimer,

Thanks so much for the help. I have spaces between the names of the worksheets and it about drove me nuts. I think I have it working now.

Thanks again for all the help!!

Bill