+ Reply to Thread
Results 1 to 6 of 6

Need to prepare recap file by pulling data from numerous tabs/files

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Need to prepare recap file by pulling data from numerous tabs/files

    I would show you my offsets and indirect formulas but they are a mess.

    See attached files. I need the "Recap" file to have the data pulled from specific rows on the "Region 1 Customers". (I will have a recap total, small size, medium, large, etc. I will have more regions with a lot more customers.

    I tried my best to explain what data I want to pull within the Recap file - See Row 3 on the SMALL tab.

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to prepare recap file by pulling data from numerous tabs/files

    I see a few problems for anyone trying to answer this, so I'll point them out, to make things easier for all of us

    I> apparently multiple regions, so a second region workbook would help to get the formulas right
    2> are the region workbooks all in the same folder (sub-directory)?
    3> is the Recap file in the same folder (sub-directory) as the region files (folders (sub-directories)) ?

    Hope this helps

    Edit -
    AND 4> can / will the region files be open when the Recap file is being run, most Excel functions require this, so it is not an idle question

    Edit 2 -
    INDIRECT() for example (being one of the ways to access changing workbooks) REQUIRES the other workbook to be open, but it is not the only one, just an example of what can be encountered
    Last edited by dredwolf; 10-31-2013 at 10:29 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Need to prepare recap file by pulling data from numerous tabs/files

    1. Second region workbook will be identical format as the first. call it "Customers Region 2"
    2. Folder directories are:
    Sales > Sales Manager 1 > Region 1, Region 2....
    Sales > Sales Manager 2 > Region 3, Region 4
    3. Recap file will be
    Sales > Recap File.xls

    Does that help?

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to prepare recap file by pulling data from numerous tabs/files

    If will definitely help as different directories affect the formulas
    but please check the edits in my last post (#2) as they are relevant as well

  5. #5
    Registered User
    Join Date
    08-16-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Need to prepare recap file by pulling data from numerous tabs/files

    Oh. See, last year I took all the customer tabs for each sales managers region and put them into one file. Then, I did the summaries for each sales managers region within that same file... So the tabs looked like this... Summary | Large | Small | Cust 1 | Cust 2... etc. Then, I just summed all the "Large" tabs for all the Sales managers in another file with simple links. Should I go that route again? - This did not make it necessary to have both files open at the same time.

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to prepare recap file by pulling data from numerous tabs/files

    Well, you could do that again (makes it very easy to work with the data if its all in one worksheet ),
    or you can figure out EXACTLY how you want the data worksheets to be arranged, then we can work from there.. what is really needed is "what you (want/need/expect)" ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Prepare one file using data from multiple files.
    By callmevj99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2013, 02:47 PM
  2. One Master File that will be cut rows and saved into numerous files
    By asian_xl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2009, 12:22 AM
  3. Pulling data from multiple files into holding file
    By Darkfish in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2009, 10:13 AM
  4. Pulling data from files by part of file name
    By James580 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2008, 04:35 PM
  5. compiling/pulling data (range) in different files into one file
    By perinouk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2007, 08:28 AM

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