+ Reply to Thread
Results 1 to 4 of 4

Lookup Functions to find data in multiple files

  1. #1
    Registered User
    Join Date
    09-01-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    2

    Lookup Functions to find data in multiple files

    Hi All,
    New to here so hopefully i have added all the info needed if not then shout.
    I currently use excel for a template calculator where i fill in values throughout the sheet and it returns a quote for me.
    What i need to do is create a basic sheet that will lookup certain cell values in every file in a set folder.
    So basically everytime i do a quote i save it as customer name then an ascending number eg customer 9-1233 next one is customer 9-1234.
    Is a function possible where it will pull values from every file within a set folder then pluck out certain cell contents?
    Any info would be appreciated.
    Regards
    Darren

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Lookup Functions to find data in multiple files

    TAFKADaz.

    I currently use excel for a template calculator where i fill in values throughout the sheet and it returns a quote for me.
    What i need to do is create a basic sheet that will lookup certain cell values in every file in a set folder.
    Please post the workbook (template calculator) where the data from the closed workbooks will be summarized in.

    What is the full path to where the customer files are stored?

    What is the sheet name in the customer files where the data will be sumarized from?

    Are the cells to be summarized in all the customer files, in the same column and rows in all the customer files?

    And, please post two sample customer files to test from.
    Last edited by stanleydgromjr; 09-01-2009 at 09:53 PM.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    09-01-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Lookup Functions to find data in multiple files

    Sorry on delay i never had it set to notifications.
    Ok hopefully this will make it easier i have attached 3 sample customer quotes.
    I have also attached a sample template of what i would like the report file to look like this includes a description of the data i need summarising and where on the customer sheet it is located.
    All of the customer quotes get saved to C:/quotations/jan 2009/
    then feb 2009 etc, i would alter the formula for each month if needed i just need to know the basic formula.
    It should then provide me with a summary for that month.
    This needs to update to alll the files within that folder when ever we open the summary so as we do more quotations they get added to the summary sheet when opened.(if possible).
    Hope all is clear.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Lookup Functions to find data in multiple files

    TAFKADaz,

    This needs to update to alll the files within that folder when ever we open the summary so as we do more quotations they get added to the summary sheet when opened.(if possible).
    See the attached workbook "Get Data from closed workbooks with formulae - Example Report - TAFKADaz - SDG10.xls" with a command button "Get Data".

    Each time you click on the "Get Data" command button, the data in Sheet1, beginning in A2, down to the last row in column A, and over to column E, will be cleared, and then re-established via formulae, and then the formulae will be changed to values.

    You will have to manually change/update the full path in cell F1, and then click on the "Get Data" command button, for other months.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1