Results 1 to 4 of 4

Thread: Formula for moving data between spreadsheets

  1. #1
    Neophyte Demtro's Avatar
    Join Date
    Jul 2011
    Location
    Clearwater, Florida, United States
    Posts
    2
    Articles
    0

    Formula for moving data between spreadsheets



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

    Hi everybody,

    I have attached 2 dummy spreadsheets to give you an idea of how i need stuff moved...
    Spreadsheet A Contains thet data that I need moved into spreadsheet B...

    In spreadsheet A are Dates, names, offices, sales, and commiussions...

    In spreadsheet B is a worksheet for one date, a second worksheet for the second date and a 3rd worksheet for the 3rd date. I also have the names of all 3 people in spreadsheet B worksheet 1, 2 and 3.

    basically what i need is this..I need a function that I can place in the Office, Sales and Commisions cell and have it copy over that employees office, sales and commisions for the date specifies in the worksheet.

    spreadsheets are attached, I have researched this for a couple weeks, on my own and need some help

    thanks

    Demtro
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Any reason it needs to be in a separate workbook? This would seem like an ideal candidate for a pivot table, based on the format that you want the report out... it would be much easier for you to maintain if it were in the same file...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte Demtro's Avatar
    Join Date
    Jul 2011
    Location
    Clearwater, Florida, United States
    Posts
    2
    Articles
    0
    SpreadsheetB not only will contain this information but it will contain other information that is typed in manually. Additionally it is layed out in a very specific manner which is required by the teams utilizing it. I thought about just sorting using a pivot table, but it has to be layed out very particularly.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Okay, so I think you could still use a PivotTable for this... just connect it to an external file. To be honest, the layout you have in SpreadsheeB is pretty simple to set up (at least in Excel 2010 anyway). And if you don't want to use the pivot table format for it, then you can always build your report using GETPIVOTDATA or INDEX/MATCH formulas off a pivot on a hidden worksheet.

    To start, you need to go an define a named range for your data table in SpreadsheetA. I called it "tblData". Then create a PivotTable in SpreadsheetB, get your data from an external source, and pull it in there.

    If you need help doing that, let me know what version of Excel you're using, and I'll get you the steps to do it for your version.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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