Formula for moving data between spreadsheets

D

Demtro

New member
Joined
Jul 7, 2011
Messages
2
Reaction score
0
Points
0
Location
Clearwater, Florida, United States
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
 

Attachments

  • SpreadsheetB.xlsx
    9.9 KB · Views: 28
  • SpreadsheetA.xlsx
    10.1 KB · Views: 48
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...
 
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.
 
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.
 
You must log in or register to reply here.
Back
Top