Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
Came across this old thread, which was similar to what I needed, but
unfortunately the solutions recommended back then involved doing the worksheet other ways. So my question is: If I have a list on one worksheet, which is then copied to a second worksheet, how do I ensure the second updates if I add new rows or change the text to the first. Kind Regards A Taxed Mind Previous thread starting Q. Subject: Copy data into another worksheet and have it update automatically? 3/30/2006 7:55 AM PST By: Lmarie6 In: microsoft.public.excel.misc My boss sent me a workbook that at present contains only one sheet, however he wants me to copy certain data (depending on column A) into a second sheet and other data into a third sheet, but have it update on sheets 2 and 3 when the data on sheet1 is changed, added, or deleted. Column A contains a general classification, and he wants certain classifications on sheet2 and other classifications on sheet3. There are no formulas, just data. It's just a big list of materials and their locations and vendors, etc. Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
I would think that some of the previous suggestions were to not do this. Keep
your data in one spot. If you need to extract the data later, you can. But do the updates only in that one location. If you really have to keep the data in multiple locations, then I think the only way to insure what you want is to do it manually and double, triple, ... check your input. Taxed Mind wrote: Came across this old thread, which was similar to what I needed, but unfortunately the solutions recommended back then involved doing the worksheet other ways. So my question is: If I have a list on one worksheet, which is then copied to a second worksheet, how do I ensure the second updates if I add new rows or change the text to the first. Kind Regards A Taxed Mind Previous thread starting Q. Subject: Copy data into another worksheet and have it update automatically? 3/30/2006 7:55 AM PST By: Lmarie6 In: microsoft.public.excel.misc My boss sent me a workbook that at present contains only one sheet, however he wants me to copy certain data (depending on column A) into a second sheet and other data into a third sheet, but have it update on sheets 2 and 3 when the data on sheet1 is changed, added, or deleted. Column A contains a general classification, and he wants certain classifications on sheet2 and other classifications on sheet3. There are no formulas, just data. It's just a big list of materials and their locations and vendors, etc. Can this be done? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
Thanks for that.
Basically I am writing myself a little (seems to get bigger all the time) business tax "calculator". I have an initial list of variables which I might buy and their sales (value added) tax rate, etc. On another sheet I have a drop down list (automatically updates) where I can select the goods purchased and enter their dates etc, other items, eg tax etc are then completed automatically. I then sum up on another sheet through a SUMIF function items added from the drop down list. However I was wanting this section to automatically update if I added any other standard goods to the intial list. As this way doesn't seem possible and I am not to keen to copy and paste into all the various sections, etc, : is there a way to relist on a worksheet items saved to a drop down list, as that is obviously updated. ACTUALLY, just thought that if I am naming a selection to enable it to be used in a drop down list: can I relist that named section on another sheet. If so that would solve my problem! Abit complicated and these things are always difficult to explain. Thanks again for your help. "Dave Peterson" wrote: I would think that some of the previous suggestions were to not do this. Keep your data in one spot. If you need to extract the data later, you can. But do the updates only in that one location. If you really have to keep the data in multiple locations, then I think the only way to insure what you want is to do it manually and double, triple, ... check your input. Taxed Mind wrote: Came across this old thread, which was similar to what I needed, but unfortunately the solutions recommended back then involved doing the worksheet other ways. So my question is: If I have a list on one worksheet, which is then copied to a second worksheet, how do I ensure the second updates if I add new rows or change the text to the first. Kind Regards A Taxed Mind Previous thread starting Q. Subject: Copy data into another worksheet and have it update automatically? 3/30/2006 7:55 AM PST By: Lmarie6 In: microsoft.public.excel.misc My boss sent me a workbook that at present contains only one sheet, however he wants me to copy certain data (depending on column A) into a second sheet and other data into a third sheet, but have it update on sheets 2 and 3 when the data on sheet1 is changed, added, or deleted. Column A contains a general classification, and he wants certain classifications on sheet2 and other classifications on sheet3. There are no formulas, just data. It's just a big list of materials and their locations and vendors, etc. Can this be done? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatica
If you're creating quantitative summaries of your data, you may want to look at
using a pivottable. And you could use a dynamic range name that is used as the basis for that pivottable. Debra Dalgleish has notes about dynamic range names: http://www.contextures.com/xlNames01.html#Dynamic And if you've never use pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Taxed Mind wrote: Thanks for that. Basically I am writing myself a little (seems to get bigger all the time) business tax "calculator". I have an initial list of variables which I might buy and their sales (value added) tax rate, etc. On another sheet I have a drop down list (automatically updates) where I can select the goods purchased and enter their dates etc, other items, eg tax etc are then completed automatically. I then sum up on another sheet through a SUMIF function items added from the drop down list. However I was wanting this section to automatically update if I added any other standard goods to the intial list. As this way doesn't seem possible and I am not to keen to copy and paste into all the various sections, etc, : is there a way to relist on a worksheet items saved to a drop down list, as that is obviously updated. ACTUALLY, just thought that if I am naming a selection to enable it to be used in a drop down list: can I relist that named section on another sheet. If so that would solve my problem! Abit complicated and these things are always difficult to explain. Thanks again for your help. "Dave Peterson" wrote: I would think that some of the previous suggestions were to not do this. Keep your data in one spot. If you need to extract the data later, you can. But do the updates only in that one location. If you really have to keep the data in multiple locations, then I think the only way to insure what you want is to do it manually and double, triple, ... check your input. Taxed Mind wrote: Came across this old thread, which was similar to what I needed, but unfortunately the solutions recommended back then involved doing the worksheet other ways. So my question is: If I have a list on one worksheet, which is then copied to a second worksheet, how do I ensure the second updates if I add new rows or change the text to the first. Kind Regards A Taxed Mind Previous thread starting Q. Subject: Copy data into another worksheet and have it update automatically? 3/30/2006 7:55 AM PST By: Lmarie6 In: microsoft.public.excel.misc My boss sent me a workbook that at present contains only one sheet, however he wants me to copy certain data (depending on column A) into a second sheet and other data into a third sheet, but have it update on sheets 2 and 3 when the data on sheet1 is changed, added, or deleted. Column A contains a general classification, and he wants certain classifications on sheet2 and other classifications on sheet3. There are no formulas, just data. It's just a big list of materials and their locations and vendors, etc. Can this be done? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy data into another worksheet and have it update automatically?
In , Dave Peterson
spake thusly [edited for flow]: Taxed Mind wrote: If I have a list on one worksheet, which is then copied to a second worksheet, how do I ensure the second updates if I add new rows or change the text to the first. I would think that some of the previous suggestions were to not do this. Keep your data in one spot. If you need to extract the data later, you can. But do the updates only in that one location. If you really have to keep the data in multiple locations, then I think the only way to insure what you want is to do it manually and double, triple, ... check your input. Hmm. Well, I'm doing exactly what "Taxed Mind" wanted, and I specifically chose to do it in a way that ensures data integrity (because I got tired of data corruption given the old, manual way I used to do it). Here's a synopsis: The data that is frequently updated that I want to be sure to keep unsullied, I import into a worksheet in the same workbook. I have a macro for this, and except when the macro runs, the worksheet is left protected and hidden. It isn't in my way, because I can't even see it unless I need to for some reason and go unhide it. That data is imported from a CSV file that I download. I have it set up as a data source for the normally hidden sheet. Okay, then in my main sheets that I want to use parts of the data from, I simply point to the hidden sheet and fill down. Now I can sort, add other columns, etc. I have a macro for refreshing the working sheet(s) and sorting. It's actually the same macro as the one above. I gave myself some visual clues as to which data is "original" and which is altered or added. The main such is, the column headers for the relevant columns that are left as in the original, I format in one color; the headers for "synthetic" columns I have there that are derived from the original data, I color- code in a different color. I also have some conditional formatting going on to compare values to make sure nothing got corrupted when I wasn't "looking out" with my formulas. I actually have a third header color for "derivative" columns that are, in turn, based on the first set of "synthetic" columns. Anyway, I can tell at a glance from the colors and the conditional formatting which data is original and which is mine. The charts also auto-update. The most recent thing I figured out was to have charts whose title[1] changes depending on which data I have filtered. Note that I don't yet know any VBA, so this is all done with standard macros and lots of trial and error. [1] I cheat here. It's not really the title that gets used, but a dummy data set whose name shows up in the legend. Dallman Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|