How to automatically update one Excel worksheet from another?
January 4, 2011 8:52 PM Subscribe
Help with Excel! Is it possible to send specific information from one worksheet to another worksheet automatically?
I'm helping my family create Excel spreadsheets to keep track of the family business. I knew next to nothing about Excel before starting this project a couple of weeks ago, and while I've learned a little bit, I still don't know if this is possible or how to do it if it is.
Say I have a worksheet titled "all expenses." Then I have other worksheets in the same file titled "rental," "repair," "labor"... or something like that. What would be really helpful is if there is some way to send all rows describing rental expenses automatically to the rental expenses worksheet. This would a) save time, and b) save headache, since I wouldn't have to rely on my family remembering to enter it in twice.
(The reason that I think we need separate worksheets is that each of these categories needs to be further subcategorized, and it's much easier for my parents to understand what they're looking at if the major categories are separated out from each other. I know that separate worksheets aren't necessary to keep track of expenses by category.)
I have some Excel books but honestly there's so much information in them that it's overwhelming to find/understand the specific information I need. Any Excel whizzes out there who can help?
Special bonus question: is it possible to do this between different Excel FILES as well?
I'm helping my family create Excel spreadsheets to keep track of the family business. I knew next to nothing about Excel before starting this project a couple of weeks ago, and while I've learned a little bit, I still don't know if this is possible or how to do it if it is.
Say I have a worksheet titled "all expenses." Then I have other worksheets in the same file titled "rental," "repair," "labor"... or something like that. What would be really helpful is if there is some way to send all rows describing rental expenses automatically to the rental expenses worksheet. This would a) save time, and b) save headache, since I wouldn't have to rely on my family remembering to enter it in twice.
(The reason that I think we need separate worksheets is that each of these categories needs to be further subcategorized, and it's much easier for my parents to understand what they're looking at if the major categories are separated out from each other. I know that separate worksheets aren't necessary to keep track of expenses by category.)
I have some Excel books but honestly there's so much information in them that it's overwhelming to find/understand the specific information I need. Any Excel whizzes out there who can help?
Special bonus question: is it possible to do this between different Excel FILES as well?
Response by poster: Is there a specific "terminology" for the kind of trick I'm asking about? I suspect that there are instructions in the WHOPPING HUGE EXCEL BOOK my mom bought, but I'm unable to find it in the index because I don't know where to look.
posted by Kutsuwamushi at 9:10 PM on January 4, 2011
posted by Kutsuwamushi at 9:10 PM on January 4, 2011
Yes. Copy --> Paste Special --> Paste Link
("link between workbooks" is maybe the term to search for? But really, I just googled "excel link to other file" to confirm the syntax and get you a cite. So it might just be knowing that it can be done so that you think to try to search for how.)
posted by misterbrandt at 9:14 PM on January 4, 2011
("link between workbooks" is maybe the term to search for? But really, I just googled "excel link to other file" to confirm the syntax and get you a cite. So it might just be knowing that it can be done so that you think to try to search for how.)
posted by misterbrandt at 9:14 PM on January 4, 2011
Response by poster: It looks like linking between workbooks is something that you have to do to individual cells. This isn't what I want to do. I still haven't found more information on what ZombieApoc suggested.
Maybe I didn't explain the situation well. I was a little vague because I didn't want anyone to get hung up on our particular setup, which can be changed.
My family keeps an expenses worksheet that is, basically, a copy of their check ledger with some additional information. An example row would look something like this
2873 - 3/14 - Bob Schmob - 123 Street Rd - Labor - (412.23)
Where Bob is the person that we paid $412.23, with check number 2873 on 3/14, for work he did on 123 Street Rd. "Labor" is a type of expense that we want to track.
I want everything marked "labor" to automatically be copied to another worksheet. The whole row, not an individual cell. Is that possible?
posted by Kutsuwamushi at 9:43 PM on January 4, 2011
Maybe I didn't explain the situation well. I was a little vague because I didn't want anyone to get hung up on our particular setup, which can be changed.
My family keeps an expenses worksheet that is, basically, a copy of their check ledger with some additional information. An example row would look something like this
2873 - 3/14 - Bob Schmob - 123 Street Rd - Labor - (412.23)
Where Bob is the person that we paid $412.23, with check number 2873 on 3/14, for work he did on 123 Street Rd. "Labor" is a type of expense that we want to track.
I want everything marked "labor" to automatically be copied to another worksheet. The whole row, not an individual cell. Is that possible?
posted by Kutsuwamushi at 9:43 PM on January 4, 2011
Best answer: Look into pivot tables, where you can set them to filter by category. From reading this page, there is apparently a "filter" option under the "data" menu (in excel 2003 at least) . . . maybe that does what you want? I'm not entirely sure. The "Auto Filter" option seems to give you a nice selectable drop-down menu to choose categories. Not separate sheets, but maybe an acceptable alternative?
Pivot tables may give you some more options though.
posted by that girl at 10:17 PM on January 4, 2011
Pivot tables may give you some more options though.
posted by that girl at 10:17 PM on January 4, 2011
I don't think excel is the right tool for the job. Quickbooks or something similar would be well-suited.
posted by bleeb at 10:20 PM on January 4, 2011 [1 favorite]
posted by bleeb at 10:20 PM on January 4, 2011 [1 favorite]
bleeb's right. excel is a poor choice and is like driving a screw with a hammer.... possible, but suboptimal.
if you INSIST on using excel, i presume you realize that there are separate sheets in a single spreadsheet (look at the bottom.... sheet1, sheet2, sheet3). The can be renamed and offer the benefit that they are in the same place.
if you are dealing with noobs, backup. often. they will kill your spreadsheet setup and you will know more about why excel is not the tool for the job after trying to fix things a few dozen times.
you could also get a bookkeeper. there's more to this than data entry.
posted by FauxScot at 4:19 AM on January 5, 2011
if you INSIST on using excel, i presume you realize that there are separate sheets in a single spreadsheet (look at the bottom.... sheet1, sheet2, sheet3). The can be renamed and offer the benefit that they are in the same place.
if you are dealing with noobs, backup. often. they will kill your spreadsheet setup and you will know more about why excel is not the tool for the job after trying to fix things a few dozen times.
you could also get a bookkeeper. there's more to this than data entry.
posted by FauxScot at 4:19 AM on January 5, 2011
Best answer: A pivot table is an excellent suggestion. Are you using 2003 or 2007 or another version?
If 2007, go to the Insert tab and hut the arrow below PivotTable and select PivotTable. In the box that pops up, make sure all of the data on the master file is selected for Table/Range (which it should be if your cursor started in a populated cell and you don't have blank lines or columns). Select new Worksheet on bottom and hit okay. What pops up is the Pivot Table field list with the names of your columns listed at the top. Take your category column and drag it to the area below that is named "Report Filter". Then hit the checkbox next to all of the other boxes and they should go directly into the Row Labels box. Close the pivot table field list using the x. Now right-click in the data area and uncheck any fields that say "Subtotal blahblah". The sheet should now look a lot like your master sheet with the category column missing. Use the drop down arrow that should be in cell B1 to select a category. Your sheet should now show you only the entries for that category. When you add info to the master sheet, you will have to refresh the pivot table by right clicking in an area with data and selecting "Refresh".
If you will be altering this data (like the subcategories you mentioned) refreshing might wipe out those changes. If you want a new sheet with the Pivot data that you can edit without care, copy the whole sheet and Paste Special... then select Values.
Also, look into filtering (Data then Filter) on your master sheet to see just the category you want at any particular time. You can even copy just those lines over to a new sheet.
posted by soelo at 10:31 AM on January 5, 2011
If 2007, go to the Insert tab and hut the arrow below PivotTable and select PivotTable. In the box that pops up, make sure all of the data on the master file is selected for Table/Range (which it should be if your cursor started in a populated cell and you don't have blank lines or columns). Select new Worksheet on bottom and hit okay. What pops up is the Pivot Table field list with the names of your columns listed at the top. Take your category column and drag it to the area below that is named "Report Filter". Then hit the checkbox next to all of the other boxes and they should go directly into the Row Labels box. Close the pivot table field list using the x. Now right-click in the data area and uncheck any fields that say "Subtotal blahblah". The sheet should now look a lot like your master sheet with the category column missing. Use the drop down arrow that should be in cell B1 to select a category. Your sheet should now show you only the entries for that category. When you add info to the master sheet, you will have to refresh the pivot table by right clicking in an area with data and selecting "Refresh".
If you will be altering this data (like the subcategories you mentioned) refreshing might wipe out those changes. If you want a new sheet with the Pivot data that you can edit without care, copy the whole sheet and Paste Special... then select Values.
Also, look into filtering (Data then Filter) on your master sheet to see just the category you want at any particular time. You can even copy just those lines over to a new sheet.
posted by soelo at 10:31 AM on January 5, 2011
Response by poster: We looked into using programs like Quickbooks but they aren't flexible enough for our needs. It was basically a huge disaster, because we sunk time and money into it but it ended up being unusable. With Excel, even if there's no perfect solution, I can at least play around until I find a workable solution.
The bookkeeper suggestion is a good one, but money is tight now.
if you INSIST on using excel, i presume you realize that there are separate sheets in a single spreadsheet (look at the bottom.... sheet1, sheet2, sheet3)
Yeah, I'm asking about how to move information between sheets. I'll try the PivotTable idea.
posted by Kutsuwamushi at 12:04 PM on January 5, 2011
The bookkeeper suggestion is a good one, but money is tight now.
if you INSIST on using excel, i presume you realize that there are separate sheets in a single spreadsheet (look at the bottom.... sheet1, sheet2, sheet3)
Yeah, I'm asking about how to move information between sheets. I'll try the PivotTable idea.
posted by Kutsuwamushi at 12:04 PM on January 5, 2011
« Older Least invasive border crossings? | My computer's running too cool! (Overclocking noob... Newer »
This thread is closed to new comments.
Also, use the vlookup command for picking out specific pieces of info from sets of data, this can also be done across sheets.
Can't do it between files as much as I know, but the new Office 2010 has some great features with connecting to DBs, so maybe opening up other Excel files behind the scenes is possible.
posted by zombieApoc at 9:06 PM on January 4, 2011