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?