Results 1 to 3 of 3

Thread: Lookup across multiple sheets/tabs in external workbook

  1. #1

    Lookup across multiple sheets/tabs in external workbook



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

    Hello

    I'm trying to pickup daily total of sales values from multiple workbooks on sharepoint to newly created workbook. Each workbook on sharepoint represent a month such as April, and has multiple worksheets/tabs named in date order such as, 01.04.14, 02.04.14.......On each of the worksheet cell F23 contains the daily sales total.

    I want a formula to lookup the daily totals for each month onto my newly created work book(ATTACHED). Grateful if someone could help.

    Thanks.
    Attached Files Attached Files

  2. #2
    Just to clarify a bit more.

    I need to find data in another workbook that has many tabs (worksheet)in day for the month, the workbook that I require the data into(attached) has a reference to the tab such as 01.04.4, 02.04.14 etc.I need to, each day retrieve data from a cell (same cell -F23)on the different tabs (sheets in the source Workbook) and place it in the column under that day on the destination worksheet(attached).

    Thanks in advance.

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    One problem here, is that this would require a function like INDIRECT(), but, unfortunately, that function does not work when referencing closed workbooks. There are, however, VBA functions that people have written to circumvent this....

    If the workbooks were open, the INDIRECT function would be something like this:

    =INDIRECT("'X:\WorkbookDirectory\["&TEXT($A2,"MMM-YY")&".xlsx]"&TEXT(B$1,"DD.MM.YY")&"'!$F$23")

    although, if you have the months listed in column A, then not sure if the headers in row 1 apply... since in your sample they reference month 4 (April).

    Anyway, the TEXT functions basically return the dates in the formats shown, as text so that you can reference the workbook and/or sheetnames properly.

    A couple of more popular functions people have written are the PULL function, and the INDIRECTEX functions. They both should work like the built in INDIRECT function, but may be less efficient.


Posting Permissions

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