Results 1 to 8 of 8

Thread: Auto complete from different Workbook

  1. #1

    Red face Auto complete from different Workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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
    Last edited by Hercules1946; 2013-08-07 at 04:38 PM. Reason: TYping error

  3. #3

    Thanksyou

    That works perfect, many thanks! Much appreciated!

    Quote Originally Posted by Hercules1946 View Post
    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

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Your welcome

  5. #5
    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

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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

  7. #7

    Cool

    Quote Originally Posted by Hercules1946 View Post
    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

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •