Baffling problem with links

S

Stephen POWELL

1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a problem before).
3. All such linking formulas show "#REF!". But everything worked fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the
INDEX arguments within the SUM formula each evaluate to the proper cells in
the source workbooks. In other words, I proceeded through EVALUATE FORMULA
until I got
SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
 
F

Frank Kabel

Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a problem before).
3. All such linking formulas show "#REF!". But everything worked fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the
INDEX arguments within the SUM formula each evaluate to the proper cells in
the source workbooks. In other words, I proceeded through EVALUATE FORMULA
until I got
Click to expand...
SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
 
S

Stephen POWELL

Thanks, Frank. I forgot to mention that when I open all the source workbooks
the linking formulas in the destination workbook produce the correct results.
You may be on to something so I went back and re-performed the EVALUATE
FORMULA and the way I first presented it is accurate. I'm still baffled.

Frank Kabel said:
Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a problem before).
3. All such linking formulas show "#REF!". But everything worked fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the
INDEX arguments within the SUM formula each evaluate to the proper cells in
the source workbooks. In other words, I proceeded through EVALUATE FORMULA
until I got
Click to expand...
SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
this is just the usage of your syntax. Your kind of cell reference is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Thanks, Frank. I forgot to mention that when I open all the source workbooks
the linking formulas in the destination workbook produce the correct results.
You may be on to something so I went back and re-performed the EVALUATE
FORMULA and the way I first presented it is accurate. I'm still baffled.

Frank Kabel said:
Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:[email protected]...
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a
Click to expand...
problem
before).
3. All such linking formulas show "#REF!". But everything worked fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the
INDEX arguments within the SUM formula each evaluate to the
Click to expand...
proper
cells in
the source workbooks. In other words, I proceeded through
Click to expand...
EVALUATE
FORMULA
until I got
Click to expand...
SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
S

Stephen POWELL

Frank:
Then you're saying it is not possible to do what I want using the structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month) in the
destination workbook each of which picks up only one cell in the source
workbook and my INDEX function would operate in the thirteenth column to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

Frank Kabel said:
Hi
this is just the usage of your syntax. Your kind of cell reference is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Thanks, Frank. I forgot to mention that when I open all the source workbooks
the linking formulas in the destination workbook produce the correct results.
You may be on to something so I went back and re-performed the EVALUATE
FORMULA and the way I first presented it is accurate. I'm still baffled.

Frank Kabel said:
Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag 1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a problem
before).
3. All such linking formulas show "#REF!". But everything worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm
that the
INDEX arguments within the SUM formula each evaluate to the proper
cells in
the source workbooks. In other words, I proceeded through EVALUATE
FORMULA
until I got

SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi Stephen
it would help if you could post the complete formula. There's probably
a different way

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Frank:
Then you're saying it is not possible to do what I want using the structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month) in the
destination workbook each of which picks up only one cell in the source
workbook and my INDEX function would operate in the thirteenth column to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

Frank Kabel said:
Hi
this is just the usage of your syntax. Your kind of cell reference is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:[email protected]...
Thanks, Frank. I forgot to mention that when I open all the
Click to expand...
source
workbooks
the linking formulas in the destination workbook produce the
Click to expand...
correct
results.
You may be on to something so I went back and re-performed the EVALUATE
FORMULA and the way I first presented it is accurate. I'm still baffled.

:

Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Stephen POWELL" <[email protected]>
Click to expand...
schrieb
im
Newsbeitrag 1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source
workbooks all in one other directory (this has never been a problem
before).
3. All such linking formulas show "#REF!". But everything worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm
that the
INDEX arguments within the SUM formula each evaluate to the proper
cells in
the source workbooks. In other words, I proceeded through EVALUATE
FORMULA
until I got
Click to expand...
SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
S

Stephen POWELL

Thanks for your time and help, Frank.
Here's the formula:
=SUM(INDEX('S:\_Finance_and_Administration_Corporate\Forecast - New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1))
Stephen

Frank Kabel said:
Hi Stephen
it would help if you could post the complete formula. There's probably
a different way

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Frank:
Then you're saying it is not possible to do what I want using the structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month) in the
destination workbook each of which picks up only one cell in the source
workbook and my INDEX function would operate in the thirteenth column to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

Frank Kabel said:
Hi
this is just the usage of your syntax. Your kind of cell reference is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Thanks, Frank. I forgot to mention that when I open all the source
workbooks
the linking formulas in the destination workbook produce the correct
results.
You may be on to something so I went back and re-performed the
EVALUATE
FORMULA and the way I first presented it is accurate. I'm still
baffled.

:

Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

im
Newsbeitrag
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several
source
workbooks all in one other directory (this has never been a
problem
before).
3. All such linking formulas show "#REF!". But everything worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm
that the
INDEX arguments within the SUM formula each evaluate to the
proper
cells in
the source workbooks. In other words, I proceeded through
EVALUATE
FORMULA
until I got


SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
S

Stephen POWELL

The same formula simplified is
=SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.xls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6:$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedPL!$B$6:$O$6,0),1))
where $B$5 = "NOV" to identify the current month (specifies the column)
and $B$9 = "Total Revenue" (specifies the row)
Thanks,
Stephen

Stephen POWELL said:
Thanks for your time and help, Frank.
Here's the formula:
=SUM(INDEX('S:\_Finance_and_Administration_Corporate\Forecast - New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\_Finance_and_Administration_Corporate\Forecast
- New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1))
Stephen

Frank Kabel said:
Hi Stephen
it would help if you could post the complete formula. There's probably
a different way

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Frank:
Then you're saying it is not possible to do what I want using the structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month) in the
destination workbook each of which picks up only one cell in the source
workbook and my INDEX function would operate in the thirteenth column to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

:

Hi
this is just the usage of your syntax. Your kind of cell reference is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag
Click to expand...
Thanks, Frank. I forgot to mention that when I open all the source
workbooks
the linking formulas in the destination workbook produce the correct
results.
You may be on to something so I went back and re-performed the
EVALUATE
FORMULA and the way I first presented it is accurate. I'm still
baffled.

:

Hi
this is not a valid reference you have created. The valid reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

im
Newsbeitrag
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several
source
workbooks all in one other directory (this has never been a
problem
before).
3. All such linking formulas show "#REF!". But everything worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm
that the
INDEX arguments within the SUM formula each evaluate to the
proper
cells in
the source workbooks. In other words, I proceeded through
EVALUATE
FORMULA
until I got


SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
and you want to sum this specified row from JAN to NOV (if this is the
current month)?. If yes: would it be feasible for you to change the
heading row to a real date (e.g. 01-Nov-2004) and just format it with
the custom format
MMM

If yes you may try then the following formula (not tested though):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(MONTH([Vancouv
er.xls]CombinedPL!$C$6:$O$6)<=$B$5)*([Vancouver.xls]CombinedPL!$C$7:$O$
500))

where B5 contains 11 in this example

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
The same formula simplified is:
=SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.x
ls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6
:$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vanc
ouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedP
L!$B$6:$O$6,0),1))
where $B$5 = "NOV" to identify the current month (specifies the column)
and $B$9 = "Total Revenue" (specifies the row)
Thanks,
Stephen

Stephen POWELL said:
Thanks for your time and help, Frank.
Here's the formula:
=SUM(INDEX('S:\_Finance_and_Administration_Corporate\Forecast - New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:
\_Finance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\
_Finance_and_Administration_Corporate\Forecast
- New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1))
Stephen

Frank Kabel said:
Hi Stephen
it would help if you could post the complete formula. There's probably
a different way

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Frank:
Then you're saying it is not possible to do what I want using the
structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month)
in the
destination workbook each of which picks up only one cell in the
source
workbook and my INDEX function would operate in the thirteenth column
to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

:

Hi
this is just the usage of your syntax. Your kind of cell reference
is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

im
Newsbeitrag
Thanks, Frank. I forgot to mention that when I open all the
source
workbooks
the linking formulas in the destination workbook produce the
correct
results.
You may be on to something so I went back and re-performed the
EVALUATE
FORMULA and the way I first presented it is accurate. I'm still
baffled.

:

Hi
this is not a valid reference you have created. The valid
reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Stephen POWELL"
Click to expand...
schrieb
im
Newsbeitrag
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several
source
workbooks all in one other directory (this has never been a
problem
before).
3. All such linking formulas show "#REF!". But everything
worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to
confirm
that the
INDEX arguments within the SUM formula each evaluate to the
proper
cells in
the source workbooks. In other words, I proceeded through
EVALUATE
FORMULA
until I got



SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
S

Stephen POWELL

Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text and not
numerics for the dates (too much work to change every model). However, I
could probably:
a) modify your approach to identify DEC and subtract this from the twelve
month total; or
b) use the existing INDEX approach to do the same thing (12 months minus DEC).
This would be a fix for now and I could re-consider the design of the source
workbooks for next year's version.
Thanks again.
Stephen

Frank Kabel said:
Hi
and you want to sum this specified row from JAN to NOV (if this is the
current month)?. If yes: would it be feasible for you to change the
heading row to a real date (e.g. 01-Nov-2004) and just format it with
the custom format
MMM

If yes you may try then the following formula (not tested though):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(MONTH([Vancouv
er.xls]CombinedPL!$C$6:$O$6)<=$B$5)*([Vancouver.xls]CombinedPL!$C$7:$O$
500))

where B5 contains 11 in this example

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
The same formula simplified is:
=SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.x
ls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6
:$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vanc
ouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedP
L!$B$6:$O$6,0),1))
where $B$5 = "NOV" to identify the current month (specifies the column)
and $B$9 = "Total Revenue" (specifies the row)
Thanks,
Stephen

Stephen POWELL said:
Thanks for your time and help, Frank.
Here's the formula:
=SUM(INDEX('S:\_Finance_and_Administration_Corporate\Forecast - New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:
\_Finance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Click to expand...
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\
_Finance_and_Administration_Corporate\Forecast
- New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1))
Stephen

:

Hi Stephen
it would help if you could post the complete formula. There's probably
a different way

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Frank:
Then you're saying it is not possible to do what I want using the
structure
SUM(INDEX(...):INDEX(...)). How unjust!!!
The alternative would be hiding twelve columns (one for each month)
in the
destination workbook each of which picks up only one cell in the
source
workbook and my INDEX function would operate in the thirteenth column
to SUM
the correct number of columns depending on which months I want.
Would working with ARRAY help here?
Thanks,
Stephen

:

Hi
this is just the usage of your syntax. Your kind of cell reference
is
just not correct for closed workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

im
Newsbeitrag
Thanks, Frank. I forgot to mention that when I open all the
source
workbooks
the linking formulas in the destination workbook produce the
correct
results.
You may be on to something so I went back and re-performed the
EVALUATE
FORMULA and the way I first presented it is accurate. I'm still
baffled.

:

Hi
this is not a valid reference you have created. The valid
reference
would look like:
SUM('path\[filename]\worksheet'!$C$27:$J$27)

You may post the complete formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Stephen POWELL"
Click to expand...
schrieb
im
Newsbeitrag
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several
source
workbooks all in one other directory (this has never been a
problem
before).
3. All such linking formulas show "#REF!". But everything
worked
fine last
week when I created the dependent workbook with its links.
4. Formula in dependent workbook is:
SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH))
5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to
confirm
that the
INDEX arguments within the SUM formula each evaluate to the
proper
cells in
the source workbooks. In other words, I proceeded through
EVALUATE
FORMULA
until I got



SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27
) and
the next EVALUATE step produced the "REF!".

Any suggestions?
Many thanks,
Stephen Powell
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
with text values it would get complicated. To be honest: Better change
your model once but to deal with too complicated formulas :). Saying
that you may try the following (though not tested):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou
v
er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6
:$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500))
 
S

Stephen POWELL

Frank:
That's a very creative idea. I will give that a try.
Thanks again,
Stephen

Frank Kabel said:
Hi
with text values it would get complicated. To be honest: Better change
your model once but to deal with too complicated formulas :). Saying
that you may try the following (though not tested):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou
v
er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6
:$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500))

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text and not
numerics for the dates (too much work to change every model). However, I
could probably:
a) modify your approach to identify DEC and subtract this from the twelve
month total; or
b) use the existing INDEX approach to do the same thing (12 months minus DEC).
This would be a fix for now and I could re-consider the design of the source
workbooks for next year's version.
Thanks again.
Stephen

:
Click to expand...
Click to expand...
 
S

Stephen POWELL

Sorry, Frank. I can't get your suggestion to work. The succession of FALSE
values evaluate to zero and are multiplied together to produce zero.
I even changed your
CombinedPL!$C$6>:$O$6,0)+5)
to
CombinedPL!$C$6>:$O$6,0)+2)
because JAN is in column C and so there are two columns (A & B) to the left
of the range that need to be adjusted for when determining the COLUMN
reference.
It had seemed so logical and hopeful!!
Thanks anyway.
Stephen


Frank Kabel said:
Hi
with text values it would get complicated. To be honest: Better change
your model once but to deal with too complicated formulas :). Saying
that you may try the following (though not tested):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou
v
er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6
:$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500))

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text and not
numerics for the dates (too much work to change every model). However, I
could probably:
a) modify your approach to identify DEC and subtract this from the twelve
month total; or
b) use the existing INDEX approach to do the same thing (12 months minus DEC).
This would be a fix for now and I could re-consider the design of the source
workbooks for next year's version.
Thanks again.
Stephen
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
if you like, email me a sample file and I'll have a look at it :)

--
Regards
Frank Kabel
Frankfurt, Germany

Stephen POWELL said:
Sorry, Frank. I can't get your suggestion to work. The succession of FALSE
values evaluate to zero and are multiplied together to produce zero.
I even changed your
CombinedPL!$C$6>:$O$6,0)+5)
to
CombinedPL!$C$6>:$O$6,0)+2)
because JAN is in column C and so there are two columns (A & B) to the left
of the range that need to be adjusted for when determining the COLUMN
reference.
It had seemed so logical and hopeful!!
Thanks anyway.
Stephen


Frank Kabel said:
Hi
with text values it would get complicated. To be honest: Better change
your model once but to deal with too complicated formulas :). Saying
that you may try the following (though not tested):
=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou
v
er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6
:$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500))

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:[email protected]...
Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text
Click to expand...
and
not
numerics for the dates (too much work to change every model). However, I
could probably:
a) modify your approach to identify DEC and subtract this from
Click to expand...
the
twelve
month total; or
b) use the existing INDEX approach to do the same thing (12
Click to expand...
months
minus DEC).
This would be a fix for now and I could re-consider the design of
Click to expand...
the
source
workbooks for next year's version.
Thanks again.
Stephen
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

#REF errors in links 0
Using the INDIRECT function across multiple worksheets 0
Formula Audit mode active in one column only; cannot kill. 2
Links only update when external workbook is open 6
Links to Dynamic Named Range = Problem 8
External Links in Excel 2007 with variable source file 5
Linking Workbooks 0
Formula works only if other sheet is open 6

Top