Creating a reference to a Workbook with a fixed location but with a changeable file name..

[elaiwa]

I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc)
They are all placed in one folder but there filename is subject to change,

I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc
to shown on AA1 in another workbook(e.g. Collected.XLS)

Is this possible knowing that workbooks filenames are changeable?
Moreover Can the value of A1 from any new workbook added to the same folder be added automatically??...

Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?

 

[MorganO]

I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc)
They are all placed in one folder but there filename is subject to change,

I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc
to shown on AA1 in another workbook(e.g. Collected.XLS)

I believe you can use the worksheet function INDIRECT to accomplish this. You would need to identify in your Collected.xls workbook the names of the other workbooks (WB1, etc) that you want data returned from. You would then use the INDIRECT function instead of the embedded workbook names:

Instead of:

=[Book1.xls]Sheet1!$A$1

Use:

=INDIRECT("["&A3&"]"&"Sheet1!$A$1")

(Where Cell A3 contains the name of the workbook)

Hope this points you in the right direction.

Owen

 

[elaiwa]

Thanks for your quick reply,

But that way I have to enter workbook names manually whenever the filename changes,

Isn't there any way to monitor and recognize the file names from inside a specific path?

What about if some more workbooks where added in the same path?Can Excel detect this?

 

[MorganO]

Yes, this could be done, but would require you to write VBA code to accomplish this. With VBA you can read files within a folder and then use the information gathered to adjust information on your worksheet. It just depend how much time you want to put into the coding effort versus doing it by hand!

 

[elaiwa]

Wow..Is it that much complicated?!

Well I'll tell you then what I need to do exactly,

The workbooks I need to take references from will be timesheets for some labors,
I want to take the value of the cell containing the total working hours from each labor time sheet and get the sum of all these values in my Collected.xls workbook,knowing that each labor time sheet is a separate workbook and that all workbooks are placed in one folder,every month around 6 labor timesheets are added to these folders and I would need to get the total working hours (All Months) for each labor..

It would be great If you know a VBA code that could make this...
Thanks very much for your time and concern,

 

[MorganO]

I will give it a shot for you. Need to know some parameters first:
- Is the folder that the labor time sheet workbooks are in always the always the same folder, or does it change each month?
- Is the 'Collected.xls' folder in the same folder as the labor time sheet .xls files?
- Will the folder contain more .xls files than need to read into the 'Collected.xls' workbook? If so, is there a way to determine what the correct .xls files are that need to be read?

Please let me know and we will work towards a solution together.

Take care.

Owen

 

[elaiwa]

Thanks a lot,
The answers for your questions respectively are as follows:
-Yes,it's the same folder,doesn't change monthly.
-Preferrable not,but no problem to make it that way,anyway it's location will be fixed.
-Supposed not,the filenames for the reference workbooks will be in the following format (employee's name-Month's name.xls) but the problem is that I need to avoid conflicts that may happen if this naming system is not followed accurately by employees,(spelling wise...etc.)

I'd be so grateful if we can achieve this,

Thanks again Owen,

Take Care.

Aly

 

[MorganO]

Aly,

I've sent you a link to a workbook to your private messages.

Owen

 

[elaiwa]

Dear Owen,
Thanks a lot for your reply but I'm afraid the link you sent to me is not working,

Can you kindly send it to my private email elaiwa@yahoo.com

Thanks,

Take care,

 

[elaiwa]

Thanks Owen,

It worked,

It looks like that it was a temporary problem,

I'll check the workbook and get back to you,

Take care,

Aly