View Full Version : Formula for moving data between spreadsheets

2011-07-07, 03:05 AM
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



Ken Puls
2011-07-07, 05:41 PM
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...

2011-07-07, 07:28 PM
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.

Ken Puls
2011-07-07, 11:08 PM
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.