| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel not copying formula reference to original workbook
I simply am trying to copy a formula from a sheet in one workbook (Ex:
"Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
have you tired going into the cell and copying the formula after highlighting
it in blue then entering it into the cell on the ohter book by dble clicking the cell you want it to go another option would be to just use the = function in your new book, if its purly to see the same results just get the cells in the new workbook to = the cells in the old which have already got the reusults calculated "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
sorry answed to question n one box ,, this was the only part ment for you
another option would be to just use the = function in your new book, if its purly to see the same results just get the cells in the new workbook to = the cells in the old which have already got the reusults calculated "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
=AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB11.xls]Main!$F$17:$F$1500))
Array entered (Ctrl+Shift, enter) Or you could use the the answer from Rich which would be the quickest option. Hope this helps, Gav. "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
Ooops small typo....
=AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB1.xls]Main!$F$17:$F$1500)) Gav. "Gav123" wrote: =AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB11.xls]Main!$F$17:$F$1500)) Array entered (Ctrl+Shift, enter) Or you could use the the answer from Rich which would be the quickest option. Hope this helps, Gav. "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
Dear Rich,
I did try to copy the formula & the reference wasn't being included (I think that's what you meant by the first part of your 2nd response). I know I could simply reference a cell in the original workbook if I was trying to get the same data, but in my case, I'm creating a new formula in a different workbook (this formula is not present in the original workbook). "Rich" wrote: sorry answed to question n one box ,, this was the only part ment for you another option would be to just use the = function in your new book, if its purly to see the same results just get the cells in the new workbook to = the cells in the old which have already got the reusults calculated "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
UPDATE to previous post:
Just to avoid any confusion from my previous post where I said I was creating a new formula: I currently have this formula in the original workbook (WB1), which is a test workbook that I am still tweaking. I eventually will remove it from WB1 and use the formula exclusively in WB2. That's why I wanted the formula copied into WB2 w/ references to the original WB1 so that when I deleted the formula from WB1, the copied formula in WB2 would not be affected. Then I could simply copy the formula in WB2 across a bunch of cells to automatically change the formula to include all the months. When copying formulas between workbooks or worksheets, isn't Excel supposed to automatically include references to the original location w/in the formulas? I checked the Options in my Excel 2000 & couldn't find any preferences to include/not include references to the original data when copying. Is there something I'm missing or is there something wrong w/ my Excel 2000? "RS" wrote: Dear Rich, I did try to copy the formula & the reference wasn't being included (I think that's what you meant by the first part of your 2nd response). I know I could simply reference a cell in the original workbook if I was trying to get the same data, but in my case, I'm creating a new formula in a different workbook (this formula is not present in the original workbook). "Rich" wrote: sorry answed to question n one box ,, this was the only part ment for you another option would be to just use the = function in your new book, if its purly to see the same results just get the cells in the new workbook to = the cells in the old which have already got the reusults calculated "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
UPDATE to previous post:
Just to avoid any confusion from my previous post where I said I was creating a new formula: I currently have this formula in the original workbook (WB1), which is a test workbook that I am still tweaking. I eventually will remove it from WB1 and use the formula exclusively in WB2. That's why I wanted the formula copied into WB2 w/ references to the original WB1 so that when I deleted the formula from WB1, the copied formula in WB2 would not be affected. Then I could simply copy the formula in WB2 across a bunch of cells to automatically change the formula to include all the months. When copying formulas between workbooks or worksheets, isn't Excel supposed to automatically include references to the original location w/in the formulas? I checked the Options in my Excel 2000 & couldn't find any preferences to include/not include references to the original data when copying. Is there something I'm missing or is there something wrong w/ my Excel 2000? "RS" wrote: Dear Rich, I did try to copy the formula & the reference wasn't being included (I think that's what you meant by the first part of your 2nd response). I know I could simply reference a cell in the original workbook if I was trying to get the same data, but in my case, I'm creating a new formula in a different workbook (this formula is not present in the original workbook). "Rich" wrote: sorry answed to question n one box ,, this was the only part ment for you another option would be to just use the = function in your new book, if its purly to see the same results just get the cells in the new workbook to = the cells in the old which have already got the reusults calculated "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
Dear Gav123,
I tried your formula but Excel was telling my the name was not valid. In order to fix this, I put single quotes around the name of the referred workbook thereby changing [WB1.xls]Main to '[WB1.xls]Main'. Below is the corrected formula: =AVERAGE(IF(('[WB1.xls]Main'!$J$17:$J$1500='[WB1.xls]Main'!$AQ$12)*(TEXT('[WB1.xls]Main'!$I$17:$I$1500,"mmmyyyy")=TEXT('[WB1.xls]Main'!AD$2,"mmmyyyy")),'[WB1.xls]Main'!$F$17:$F$1500)) Thanks for showing me where the references go. I tried Rich's suggestions, but neither of them worked (see my 2 posts above). When copying formulas between workbooks or worksheets, isn't Excel supposed to automatically include references to the original location w/in the formulas? I checked the Options in my Excel 2000 & couldn't find any preferences to include/not include references to the original data when copying. Is there something I'm missing or is there something wrong w/ my Excel 2000? "Gav123" wrote: Ooops small typo.... =AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB1.xls]Main!$F$17:$F$1500)) Gav. "Gav123" wrote: =AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB11.xls]Main!$F$17:$F$1500)) Array entered (Ctrl+Shift, enter) Or you could use the the answer from Rich which would be the quickest option. Hope this helps, Gav. "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel not copying formula reference to original workbook
Dear Gav123,
I tried your formula but Excel was telling my the name was not valid. In order to fix this, I put single quotes around the name of the referred workbook thereby changing [WB1.xls]Main to '[WB1.xls]Main'. Below is the corrected formula: =AVERAGE(IF(('[WB1.xls]Main'!$J$17:$J$1500='[WB1.xls]Main'!$AQ$12)*(TEXT('[WB1.xls]Main'!$I$17:$I$1500,"mmmyyyy")=TEXT('[WB1.xls]Main'!AD$2,"mmmyyyy")),'[WB1.xls]Main'!$F$17:$F$1500)) Thanks for showing me where the references go. I tried Rich's suggestions, but neither of them worked (see my 2 posts above). When copying formulas between workbooks or worksheets, isn't Excel supposed to automatically include references to the original location w/in the formulas? I checked the Options in my Excel 2000 & couldn't find any preferences to include/not include references to the original data when copying. Is there something I'm missing or is there something wrong w/ my Excel 2000? "Gav123" wrote: Ooops small typo.... =AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB1.xls]Main!$F$17:$F$1500)) Gav. "Gav123" wrote: =AVERAGE(IF(([WB1.xls]Main!$J$17:$J$1500=[WB1.xls]Main!$AQ$12)*(TEXT([WB1.xls]Main!$I$17:$I$1500,"mmmyyyy")=TEXT([WB1.xls]Main!AC$2,"mmmyyyy")),[WB11.xls]Main!$F$17:$F$1500)) Array entered (Ctrl+Shift, enter) Or you could use the the answer from Rich which would be the quickest option. Hope this helps, Gav. "RS" wrote: I simply am trying to copy a formula from a sheet in one workbook (Ex: "Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some of the other posts about copying formulas, I want Excel to include the reference to the original workbook. I thought I've done this before, but for whatever reason, I can't seem to get it to work. The formula I want to copy is an array-based formula (though I don't think this would be the reason why this isn't working). I've even tried copying a non-array-based formula & the same thing is happening. Here's my array-entered formula that I'm trying to copy: =AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0)) Shouldn't simply copying & pasting the formula from 1 workbook to the other include the reference to the original workbook? If not, where do I need to put the references to the other workbook w/in the formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 : How to remove the digits from original reference ? | Excel Discussion (Misc queries) | |||
| how can I paste to a different workbook without formula refrencing the original sheet | New Users to Excel | |||
| Copying a chart and unlinking it from the original workbook | Charts and Charting in Excel | |||
| copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
| Excel Reference points to original file | Excel Discussion (Misc queries) | |||
Linear Mode
