Excel not copying formula reference to original workbook

R

RS

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$1500))

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?
 
R

Rich

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
 
G

Gav123

=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.
 
G

Gav123

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 said:
=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 said:
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$1500))

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?
Click to expand...
Click to expand...
 
R

RS

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).
 
R

RS

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?
 
R

RS

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 said:
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 said:
=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 said:
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$1500))

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet relative reference 3
How to change a reference when the sheet change the folder? 2
Formulas That Reference ADJACENT Sheet 4
copied formulas refer to destination sheet not source sheet 2
Excel help needed: Skipping cell without skipping cell reference 1
Array Formula + Indirect Reference 0
how to copy 3 spreadsheets and get the formulas to work 1
Sumif apllying to all sheets in workbook 2

Top