• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Call Data from a Cell in a different (closed)workbook depending on cell value.

J

Justin Greenwood

Member
Ok This is a strange one. The main file never changes name. You just put in the date it works out the week you are on and the week before.

Now the tricky part, I need to fill in a bunch of cells from the file PTWK## where ## = last week week number
So if last weeks number is in B3 on the master and I want to get data from the PTWK file say from DAY!$F$6 what formula would I need

=[PTWK'$B3$3'.xlsm]DAY!$F$6
 
Hi Justin,

If workbook is closed (PTWK) then you should use SUMPRODUCT formula as SUMPRODUCT is only one formula who can fetch data from closed workbook.

=SUMPRODUCT(1*'C:\Users\urname\Desktop\[PTWK.xlsx]Sheet1'!A1)



Justin Greenwood said:
Ok This is a strange one. The main file never changes name. You just put in the date it works out the week you are on and the week before.

Now the tricky part, I need to fill in a bunch of cells from the file PTWK## where ## = last week week number
So if last weeks number is in B3 on the master and I want to get data from the PTWK file say from DAY!$F$6 what formula would I need

=[PTWK'$B3$3'.xlsm]DAY!$F$6
Click to expand...
 
Thats great but I need to get part of the file name that is being called from a cell in the open workbook.

IE. it is Week 39 now so the file being called would be PTWK38.xlsx and next week it will change to PTWK39.

The current week number is in a cell(F3) on the open sheet.
 
There's several ways to do it. If source data is in table format.

1. Use PowerQuery with dynamic parameter set up to pull data (If you can upload sample source file and sample output file. In can help you set it up).

2. Via VBA ADO.Connection (if you search there's plenty of references out there)

3. Formula method, but depending on set up, may not be possible. Will need to see the files.
 
No, there is no way to get information from closed sheet with dynamic range.

There is only one formula which reconstruct range or you can say convert text to range INDIRECT but in Excel does not reference closed workbooks.

I would suggest , either you define sheet name on top somewhere and hide and use above suggested formula

or just use Chihiro suggested things

Thanks

Justin Greenwood said:
Thats great but I need to get part of the file name that is being called from a cell in the open workbook.

IE. it is Week 39 now so the file being called would be PTWK38.xlsx and next week it will change to PTWK39.

The current week number is in a cell(F3) on the open sheet.
Click to expand...
 
Thanks guys. With the way this looks like it is going I have moved it over to a database format and will be doing it from there followed by excel data extract so it looks good for management!
 
You must log in or register to reply here.
Back
Top