Auto complete from different Workbook

[eckfox]

I am trying to use the Auto Complete function to put the same cell from different sheets (In months) into a different Workbook.

I have a Workbook with 12 sheets for the individual months, each sheet displays the individual days of the Month and I have a figure I would like to transfer from the same cell on each sheet, I would like to pull the same cell from each sheet into a new Workbook. i.e. at th emoment in th enew Workbook I have the following formula:

='OMEXCEL\daily 2013\[July.xls]1st'!$I$21

Where the Workbook is called July.xls and the sheet is the 1st, the cell is I21, so for the next cell in the new Workbook the formula is:

='OMEXCEL\Daily 2013\[July.xls]2nd'!$I$21
and so on,

I would like this done for multiple sheets for every Month. When I drag the cursor down to auto complete it does not update to the next sheet with same cell reference.

Hope this makes sense!

Eckfox

[Hercules1946]

Hi
I think that the problem is that the autocomplete has no logical way to evaluate whether or not your sheet reference should be changed, so you need to assist with this. In the template for the different workbook, if you make a listing of the sheet names from the original workbook (eg in Cells A1 to A12, reading 1st, 2nd etc), we can then build a series of formulae in e.g. cells B1 to B12 using the INDIRECT() function. If you type the values 28 to 31 respectively into sheets 1st to 4th of book July.xls at cell I21, and ensure that this workbook is left open.
Then in the new workbook enter the following formula in Cell B1 of the new workbook:

=INDIRECT("'[July.xls]"&A1&"'!$I$21") which will return 28.
Copying down B2 to B4 will return 29, 30 and 31.

HTH

[eckfox]

That works perfect, many thanks! Much appreciated!

Hi
I think that the problem is that the autocomplete has no logical way to evaluate whether or not your sheet reference should be changed, so you need to assist with this. In the template for the different workbook, if you make a listing of the sheet names from the original workbook (eg in Cells A1 to A12, reading 1st, 2nd etc), we can then build a series of formulae in e.g. cells B1 to B12 using the INDIRECT() function. If you type the values 28 to 31 respectively into sheets 1st to 4th of book July.xls at cell I21, and ensure that this workbook is left open.
Then in the new workbook enter the following formula in Cell B1 of the new workbook:

=INDIRECT("'[July.xls]"&A1&"'!$I$21") which will return 28.
Copying down B2 to B4 will return 29, 30 and 31.

HTH

[Hercules1946]

Your welcome

[eckfox]

Hi Hercules, thanks for your help with the cell reference problem, it is working fine but I would like it to work with the reference file closed. I have tried adding the full path to the formula but doesn't seem to be working:

=INDIRECT("'C:\COSTING\Daily 2013\[may.xls]"&A1&"'!$I$21")

Where the reference file may.xls is in the folder 'Daily 2013' on the C drive. Any help is very much appreciated.

Cheers

Eckfox

[Hercules1946]

Hi
Im afraid that the indirect function only works on open workbooks. The other approach I've seen would be to use a custom function added to a standard VBA module. I'll need a bit of time to dig out the code and get back to you.


Hercules

[eckfox]

Hi
Im afraid that the indirect function only works on open workbooks. The other approach I've seen would be to use a custom function added to a standard VBA module. I'll need a bit of time to dig out the code and get back to you.


Hercules

ok many thanks again.

eckfox

[Hercules1946]

Hi
I managed to find the code and Ive modified it to (hopefully) do what you want. Ive created a workbook called July.xls that has 7 sheets with data in I21 to be your closed data file. wherever you save this file has to be identified
in Sheet1 of the main file (Closed.xls). Have a play around and see if this is what you want. If it is, we just need to copy the code module onto the sheet you'll be using for the data controls/lists Ive set up. The sheet where the extracted data goes will be wherever the active cell is, but you'll need the command button here, unless your happy to run the macro from the toolbar. Im not sure what version of Excel you have, but if its 2007 or later you might need to use the .xlsm extension.
Your security will also need setting to 'Macros Enabled'.

Let me know how you get on

Hercules