Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to keep the formulas and change their source data?
Hello, thank you in advanc for taking the time to read this.
I have several worksheets that I use to extract data from various CSV file imports and manipulate it into the form I need for my work. This saves a boatload of time over hand-extracting the data from several sources and typing it into Excel. HOWEVER. Let's say for example I have 100 items I need to extract data from. Sheet1 is a 100-item CSV import Sheet 2 is another 100-item CSV import (from another source with other data relative to the same 100-item list) Sheet3 is the real"worksheet" that includes both the extracted data from sheets 1 & 2 and the calcs I need to use in my report. Say sheet3!a:10 is something like =sheet1!a:35+sheet2!b37 in this case, the cell would have the value of $500.00 Now, say EVERY TIME I do a new report, I need to use different 100-item CSV files in sheets 1 and 2. But the data is in the same format, so the calcs on sheet 3 would work. sheet3!a:10 would still encounter nuimbers (though different) at =sheet1!a:35+sheet2!b37, and the NEW figure should be say $480.00 BUT it doesn't work that way. Because when you copy over the old 100-item lit with a new 100-item list, all the calcs on sheet3 go to #REF! and you have to re-do all your calcs. Is there any way to get Excel to act on the cell reference rather than content and so be able to paste in new data (which might, by the way, have the same value entirely) so that I can use sheets 1 and 2 as true data source sheets without re-doing all the calcs in sheet 3? Thanks gin in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to keep the formulas and change their source data?
"Shannon" wrote in message
... Hello, thank you in advanc for taking the time to read this. I have several worksheets that I use to extract data from various CSV file imports and manipulate it into the form I need for my work. This saves a boatload of time over hand-extracting the data from several sources and typing it into Excel. HOWEVER. Let's say for example I have 100 items I need to extract data from. Sheet1 is a 100-item CSV import Sheet 2 is another 100-item CSV import (from another source with other data relative to the same 100-item list) Sheet3 is the real"worksheet" that includes both the extracted data from sheets 1 & 2 and the calcs I need to use in my report. Say sheet3!a:10 is something like =sheet1!a:35+sheet2!b37 in this case, the cell would have the value of $500.00 Now, say EVERY TIME I do a new report, I need to use different 100-item CSV files in sheets 1 and 2. But the data is in the same format, so the calcs on sheet 3 would work. sheet3!a:10 would still encounter nuimbers (though different) at =sheet1!a:35+sheet2!b37, and the NEW figure should be say $480.00 BUT it doesn't work that way. Because when you copy over the old 100-item lit with a new 100-item list, all the calcs on sheet3 go to #REF! and you have to re-do all your calcs. Is there any way to get Excel to act on the cell reference rather than content and so be able to paste in new data (which might, by the way, have the same value entirely) so that I can use sheets 1 and 2 as true data source sheets without re-doing all the calcs in sheet 3? I'm confused as to how you are getting the #REF. I can paste new data into sheet 1 and the references in sheet 3 remain intact. As a matter of interest, do you get the same result if you Paste Special/ Values as you get when you just use Paste? -- David Biddulph |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to keep the formulas and change their source data?
Actually -- I didn't explain it correctly the first time... I import
the data from a CSV. But in explaining it incorrectly, it led to you giving me and idea. I will try to import the data into another book and the copy/paste values into the "source" sheet of the main book.. Thanks for the idea... Shannon On Sat, 20 May 2006 15:28:18 +0100, "David Biddulph" wrote: "Shannon" wrote in message .. . Hello, thank you in advanc for taking the time to read this. I have several worksheets that I use to extract data from various CSV file imports and manipulate it into the form I need for my work. This saves a boatload of time over hand-extracting the data from several sources and typing it into Excel. HOWEVER. Let's say for example I have 100 items I need to extract data from. Sheet1 is a 100-item CSV import Sheet 2 is another 100-item CSV import (from another source with other data relative to the same 100-item list) Sheet3 is the real"worksheet" that includes both the extracted data from sheets 1 & 2 and the calcs I need to use in my report. Say sheet3!a:10 is something like =sheet1!a:35+sheet2!b37 in this case, the cell would have the value of $500.00 Now, say EVERY TIME I do a new report, I need to use different 100-item CSV files in sheets 1 and 2. But the data is in the same format, so the calcs on sheet 3 would work. sheet3!a:10 would still encounter nuimbers (though different) at =sheet1!a:35+sheet2!b37, and the NEW figure should be say $480.00 BUT it doesn't work that way. Because when you copy over the old 100-item lit with a new 100-item list, all the calcs on sheet3 go to #REF! and you have to re-do all your calcs. Is there any way to get Excel to act on the cell reference rather than content and so be able to paste in new data (which might, by the way, have the same value entirely) so that I can use sheets 1 and 2 as true data source sheets without re-doing all the calcs in sheet 3? I'm confused as to how you are getting the #REF. I can paste new data into sheet 1 and the references in sheet 3 remain intact. As a matter of interest, do you get the same result if you Paste Special/ Values as you get when you just use Paste? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to keep the formulas and change their source data?
I import CSV data from an instrument and use macro keys to delete everything
that is to the right of the fourth column. In the first three columns are macro buttons which replicate the formulas (assuming that the same data is in the same locations every time. The macro buttons clear out data, perform calculations, prepare graphs, etc. I used the recorder to create the macros and paste them into the buttons. Paul "Shannon" wrote in message ... Hello, thank you in advanc for taking the time to read this. I have several worksheets that I use to extract data from various CSV file imports and manipulate it into the form I need for my work. This saves a boatload of time over hand-extracting the data from several sources and typing it into Excel. HOWEVER. Let's say for example I have 100 items I need to extract data from. Sheet1 is a 100-item CSV import Sheet 2 is another 100-item CSV import (from another source with other data relative to the same 100-item list) Sheet3 is the real"worksheet" that includes both the extracted data from sheets 1 & 2 and the calcs I need to use in my report. Say sheet3!a:10 is something like =sheet1!a:35+sheet2!b37 in this case, the cell would have the value of $500.00 Now, say EVERY TIME I do a new report, I need to use different 100-item CSV files in sheets 1 and 2. But the data is in the same format, so the calcs on sheet 3 would work. sheet3!a:10 would still encounter nuimbers (though different) at =sheet1!a:35+sheet2!b37, and the NEW figure should be say $480.00 BUT it doesn't work that way. Because when you copy over the old 100-item lit with a new 100-item list, all the calcs on sheet3 go to #REF! and you have to re-do all your calcs. Is there any way to get Excel to act on the cell reference rather than content and so be able to paste in new data (which might, by the way, have the same value entirely) so that I can use sheets 1 and 2 as true data source sheets without re-doing all the calcs in sheet 3? Thanks gin in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Source / Update Now buttons not available | Links and Linking in Excel | |||
automtically change ranges in formulas when new data is entered | Excel Worksheet Functions | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
Sorting Data that feeds into other formulas.... | Excel Discussion (Misc queries) | |||
When I change my data, my formulas don't update the answers,why? | Excel Discussion (Misc queries) |