Formula works only if other sheet is open

[Vince]

The formula below only updates the cell if the worksheet "Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)

 

[Don Guillett]

Try using a defined name in the destination file that refers to range in the
source file. It will also make your formulas easier

RcolH
=[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553

 

[Arvi Laanemets]

Hi

Into your workbook add a (hidden) worksheet with duplicate of your source
data in external workboook (use links for this, and duplicate only columns
you need). Now rewrite your formulas, so they refer to this (hidden) sheet.

Example:
Create sheet Links
Links!A1=IF([Requisitions.xls]Requisitions!$H6="","",[Requisitions.xls]Requisitions!$H6)
Links!B1=IF([Requisitions.xls]Requisitions!$F6="","",[Requisitions.xls]Requisitions!$F6)
Links!C1=IF([Requisitions.xls]Requisitions!$G6="","",[Requisitions.xls]Requisitions!$G6)

Copy Links!A1:C1 down for at least as much rows as in your source table.

Your formula will be
=SUMPRODUCT(--(Links!$A$1:$A$6547=A2),--(MID(Links!$C$1:$C$6547,7,5)="80801"),Links!$B$1:$B$6547)



Arvi Laanemets

 

[Dave Peterson]

I don't see why your formula requires the sending workbook to be open. Are you
sure that Requisitions.xls is really a normal workbook? Could it be a CSV file
just renamed to .xls? (It kind of looks that way based on the name of the
workbook and worksheet.)

If it's a normal workbook, then your formula is normally written like:

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:$H$6553=A2),
--(MID([Requisitions.xls]Requisitions!$G$6:$G$6553,7,5)="80801"),
[Requisitions.xls]Requisitions!$F$6:$F$6553)

The formula below only updates the cell if the worksheet "Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)

 

[Vince]

Don,

I defined names and entered the following formula with the same results. I
get #Ref in the cell until I open the other workbook. Great tip on making
the formula shorter and more readable. Any other tips on what may help?

=SUMPRODUCT(--(RcolH=A2),--(MID(RcolG,7,5)="80801"),RcolF)

Try using a defined name in the destination file that refers to range in the
source file. It will also make your formulas easier

RcolH
=[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

The formula below only updates the cell if the worksheet
"Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have
are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)

 

[Don Guillett]

I just created the defined name thusly. If you leave out the path, excel
will add it for you .
='C:\yourfoldernamehere\[bookA.xls]Sheet1'!$H$2:$H$22
and used this formula even after closing and re-opening Excel with bookA
closed.
=SUMPRODUCT(--(rcolh=$I$13),--(MID(rcolg,7,5)="12345"),rcolf)
maybe
=SUMPRODUCT(--(rcolh=$I$13),--(MID(trim(rcolg),7,5)="12345"),rcolf)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don,

I defined names and entered the following formula with the same results.
I
get #Ref in the cell until I open the other workbook. Great tip on making
the formula shorter and more readable. Any other tips on what may help?

=SUMPRODUCT(--(RcolH=A2),--(MID(RcolG,7,5)="80801"),RcolF)

Try using a defined name in the destination file that refers to range in
the
source file. It will also make your formulas easier

RcolH
=[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

The formula below only updates the cell if the worksheet
"Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open
the
current worksheet but all that is displayed is #REF in each cell that
the
formula exists. Is there a way around this? I would like not to have
to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H
=
the value in A2 and column G = 80801. Any help or suggestions you have
are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)

 

[Vince]

Dave,

Your solution worked great. I see my mistake now. Thanks so much for your
help.

I don't see why your formula requires the sending workbook to be open. Are you
sure that Requisitions.xls is really a normal workbook? Could it be a CSV file
just renamed to .xls? (It kind of looks that way based on the name of the
workbook and worksheet.)

If it's a normal workbook, then your formula is normally written like:

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:$H$6553=A2),
--(MID([Requisitions.xls]Requisitions!$G$6:$G$6553,7,5)="80801"),
[Requisitions.xls]Requisitions!$F$6:$F$6553)

The formula below only updates the cell if the worksheet "Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)