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" wrote:
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" schrieb im
Newsbeitrag ...
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" wrote:
Thanks for your time and help, Frank.
Here's the formula:
=SUM(INDEX('S:\_Finance_and_Administration_Corpora te\Forecast - New
2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_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:\_Fin
ance_and_Administration_Corporate\Forecast
- New 2004
Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin
ance_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" wrote:
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"
schrieb im
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
"Frank Kabel" wrote:
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"
schrieb
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.
"Frank Kabel" wrote:
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"
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
|