How can I link data in one workbook on the same spreadsheet to another workbook so that I can create a report?
What I need to do is take all information from columns A through I, but only if the value in column H is a particular department. I bought an extensive Excel for dummies guide, but it’s not helping.
I don’t want to have to manually move selected data, I want it to auto transfer input data onto new workbooks within the spreadsheet, so for example, I put in expenses for April in the main tab, but then it auto transfers the same data row to a corresponding worksheet with a tab labeled for the department. Say I have 10 expenses and 3 are administrative and 7 are sales, I want the 3 admin rows to copy to a new book with an Admin tab to be totalled separately, and I want the 7 sales expenses to automatically be copied to a “sales expenses” tab, a separate tab in the same spreadsheet. This way I can see expenses broken down by department rather than by date or amount, and I can print copies of these reports for the heads of the departments, rather than showing them the expenses for the whole company. Does this make sense?
Part of the problem is that I don’t know the technical words for the process I’d like to create.
I want to take the information in row A1 through I1 and transfer it to a new workbook tab, but only if H1=Admin
For the life of me I can’t figure out what type of formula this would be!
Thanks Sarah M. I didn’t know that that option existed and I will be happy to use that for other reports in the future; however, when I do that, the sum at the bottom of the page reflects ALL entries, not just the sum for sales or admin, so I think I need to figure out how to filter the information and move it to a new tab to be totalled.
Open up both workbooks. In the one that you want the linked data to appear type an = in that cell, then maximize the other workbook in which you want to link to and select that cell from your Source workbook.
Hit enter after selecting that cell and you should have the link back in your other workbook.
Or you could copy the original, go to the other workbook select paste special, then Paste link.
Whichever is easiest.
If you just need to print the reports (and not provide actual spreadsheets), then AutoFilter might be a good solution. It is an incredibly easy but powerful feature that (if you don’t know it already) you will be delighted to learn, even if it doesn’t suit this particular task.
Select your table, go to:
> Data >Filter >AutoFilter
and that’s it.
This will let you filter your list by any of the items in your table. To calculate grand totals and such, excluding the hidden rows, use Subtotal()
=SUBTOTAL (9, A1:A100)
(the “9″ stands for SUM, there are several different operations it can do).
Alternatively, if this isn’t the solution for you, what you are looking for is called a VB macro code. You need the code to copy certain rows, based on criteria, to other sheets. The way this particular question is titled, you might not get the attention from the VB guru’s on this forum, so you might want to post a new question.
——–edit———
The SUBTOTAL() formula can be used instead of the SUM() formula. It will ignore all rows hidden by filter.