=Sum(if(

[Guest]

Hi. This is Pritesh from India. I need to know whether after using "=Sum(if("
formuls (array), which is linked to external file, can we change it's source
file by "Edit> Links> Change Source"?

In other words, I have used "=Sum(if(" formula to derive values from
external excel file (used array method in it). Now I want to change source
file from Jul06 to new file Aug06. I am doing this by going to Edit> Links>
Change Source, but, getting error "Reference is not valid. Reference must be
to an open worksheet"

How can I resolve this. Please help.

 

[Bob Phillips]

Isn't the file that it is linked to in the formula? Just change it there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Bob,

If you mean to say I should edit formula manually, I can't do it for
thousands of instances in around 100 worksheets, as I am about to use this
formula in my Office's MIS reports.

Till date I was using "=Sumif(" formula, which gives me "#Value" error if my
source files are closed. Once I open all 4 to 10 of my source files,
"=Sumif(" formula works out. Someone suggested me to use "=Sum(if(" formula
with array method, so, I will not require opening my source files to get
results.

See if you can get me solution or explanation to this error.

Thanx for response.

 

[Bob Phillips]

This is quite different to what you originally stated.

If you are summing in closed workbooks, change the SUM(IF to SUMPRODUCT,
such as, instead of

=SUMIF(lookup_range,lookup_value,sum_range)

to

=SUMPRODUCT(--(lookup_range=lookup_value),sum_range)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)