Concantenated SUM() problem

[Grymjack]

I'm trying to build a sum range statement by concantenating a range from
another sheet. The actual statement looks like this

=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))

AF31 = Orders_June_2006.xls
AF32 = Orders_June_2006.xls

......in another scenario those could have different names, but lets
ignore that for now

AL31 = 12
AL31 = 22

.....these two cell values represent day codes
.....after formula evaluation it looks like this

=SUM(Orders_June_2006.xls!O_6697_12:Orders_June_2006.xls!O_6697_22)

The cells are named cells based on product code and day. The formula
evaluation doesn't throw an error, but it never evaluates to anything
but zero. When I type the formula manually without the concatenation, I
get the number I'm supposed to. What is going wrong?? Thanks.

-Dan

 

[Pete_UK]

First of all, if you use INDIRECT to another file, the file has to be
open. Also, the correct reference to a file is to enclose it in square
brackets, so if AF31 contains "Orders_June_2006.xls", then the first
part of your formula would have to be:

=SUM(INDIRECT("["&AF31&"]!O_"&INDEX($A:$A,ROW() etc,

and similarly for your other reference to the file.

Hope this helps.

Pete

 

[Grymjack]

~ Nope that didn't seem to make a difference

First of all, if you use INDIRECT to another file, the file has to be
open. Also, the correct reference to a file is to enclose it in square
brackets, so if AF31 contains "Orders_June_2006.xls", then the first
part of your formula would have to be:

=SUM(INDIRECT("["&AF31&"]!O_"&INDEX($A:$A,ROW() etc,

and similarly for your other reference to the file.

Hope this helps.

Pete

I'm trying to build a sum range statement by concantenating a range from
another sheet. The actual statement looks like this

=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))

AF31 = Orders_June_2006.xls
AF32 = Orders_June_2006.xls

.....in another scenario those could have different names, but lets
ignore that for now

AL31 = 12
AL31 = 22

....these two cell values represent day codes
....after formula evaluation it looks like this

=SUM(Orders_June_2006.xls!O_6697_12:Orders_June_2006.xls!O_6697_22)

The cells are named cells based on product code and day. The formula
evaluation doesn't throw an error, but it never evaluates to anything
but zero. When I type the formula manually without the concatenation, I
get the number I'm supposed to. What is going wrong?? Thanks.

-Dan

 

[Ron Rosenfeld]

When I type the formula manually without the concatenation

What does that mean?

Are you still using the SUM function when you get the correct answer?

If not, then the values you are SUMming are TEXT (even though it may look like
a number).






--ron

 

[Grymjack]

That means that I enter the same values that are derived from the
concatenation as a manual sum formula. Then it works. That was just
really a proof that there wasn't something else stupid going on with
where the values were being summed.

 

[Ron Rosenfeld]

That means that I enter the same values that are derived from the
concatenation as a manual sum formula. Then it works. That was just
really a proof that there wasn't something else stupid going on with
where the values were being summed.

It's still not clear to me what you mean by that. You could be entering the
addresses to which the INDIRECT function refers, or the actual numeric values
that you see in those cells.

The problem is with your data. Most likely the data you are trying to SUM is
text, although it may appear numeric. But you have not given me enough to make
a determination.

How about an example of the formula that works?
How about the contents (formula if there is one) of the cell where the data is
stored?
--ron

 

[Grymjack]

Keep in mind the goal is to create a SUM() formula through concatenation.


=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))
^^^^^ actual formula

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))
^^^^^ how my formula should look after evaluation

.....for the life of me I couldn't get the quotes and the ampersands to
place themselves correctly. I even tried appending char(34) and
char(38). Quotes get messed up in the formula evaluation somehow. I even
tried to concatenate the whole indirect() string seperately in another
column. That still didn't work. Here are all the variables I'm working
with....

***** 1
="Orders_"&TEXT($F$1,"mmmm")&"_"&TEXT($F$1,"yyyy")&".xls!"
......this calculates the file name F1 is a date

***** 2
="O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1,"dd")
......this calculates the beginning cell name in the range
......INDEX($A:$A,ROW()) comes up with a product code in whatever row the
formula is in, lets call the product code '6697'
......Again F1 is a date, now formated to give a 'Day of the Month'
appended on the back of the product code

***** 3
="O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1 + 10,"dd")
......this calculates the ending cell name in the range, otherwise works
the same as the formula above.

***** 4
=SUM(INDIRECT(""&AF31&""&"O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1,"dd")&""))
......this is the only formula I could get a number from. This formula
only gives the first cell name in the range and the numeric result is
indeed only the first cell value in the range. Any attempt to follow
your example was met with reference errors, etc.
......AF31 in this formula is the result from ****** 1 formula

Evidentially I am congenitally stupid and can't get this to work. If
you could reproduce the problems and see what I'm talking about I would
appreciate it immensely. I could even send you the original sheets if
that would help.

 

[Ron Rosenfeld]

Keep in mind the goal is to create a SUM() formula through concatenation.


=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))
^^^^^ actual formula

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))
^^^^^ how my formula should look after evaluation

....for the life of me I couldn't get the quotes and the ampersands to
place themselves correctly. I even tried appending char(34) and
char(38). Quotes get messed up in the formula evaluation somehow. I even
tried to concatenate the whole indirect() string seperately in another
column. That still didn't work. Here are all the variables I'm working
with....

If you want to produce a string that looks like:

"Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"

with

AF31: Orders_June_2006.xls
AF32: Orders_June_2006.xls
AL31: 19
AL32: 29
INDEX($A:$A,ROW()) evaluating to 6697

then the concatenation would look like:

=""""&AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32&""""

Does your second formula above

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))

give you the answers you expect?

Is Orders_June_2006.xls a worksheet? It seems to be the name of a workBOOK but
you could name a worksheet with that name.

If it is a workbook, are you aware that INDIRECT cannot be used with closed
workbooks?

***** 1
="Orders_"&TEXT($F$1,"mmmm")&"_"&TEXT($F$1,"yyyy")&".xls!"
.....this calculates the file name F1 is a date

***** 2
="O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1,"dd")
.....this calculates the beginning cell name in the range
.....INDEX($A:$A,ROW()) comes up with a product code in whatever row the
formula is in, lets call the product code '6697'
.....Again F1 is a date, now formated to give a 'Day of the Month'
appended on the back of the product code

***** 3
="O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1 + 10,"dd")
.....this calculates the ending cell name in the range, otherwise works
the same as the formula above.

***** 4
=SUM(INDIRECT(""&AF31&""&"O_"&INDEX($A:$A,ROW())&"_"&TEXT($F$1,"dd")&""))
.....this is the only formula I could get a number from. This formula
only gives the first cell name in the range and the numeric result is
indeed only the first cell value in the range. Any attempt to follow
your example was met with reference errors, etc.
.....AF31 in this formula is the result from ****** 1 formula

Evidentially I am congenitally stupid and can't get this to work. If
you could reproduce the problems and see what I'm talking about I would
appreciate it immensely. I could even send you the original sheets if
that would help.

--ron

 

[Grymjack]

If you want to produce a string that looks like:

"Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"

with

AF31: Orders_June_2006.xls
AF32: Orders_June_2006.xls
AL31: 19
AL32: 29
INDEX($A:$A,ROW()) evaluating to 6697

then the concatenation would look like:

=""""&AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32&""""

used your formula in the aw13 cell

aw13 = "Orders_June_2006.xls!O_1970_19:Orders_June_2006.xls!O_1970_29"
=SUM(INDIRECT(AW8))
#REF Error
Formula Evaluates to
sum(indirect((""Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29""))

Does your second formula above

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))

....When I use that formula the result is 10. It should be 100, 10 for
each date cell. So for some reason it is just grabbing the first cell
value?

=SUM(Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29)

....that however returns a value of 100 when used directly

Is Orders_June_2006.xls a worksheet? It seems to be the name of a workBOOK but
you could name a worksheet with that name.

It is a seperate workbook and I have no sheets with that name

If it is a workbook, are you aware that INDIRECT cannot be used with closed
workbooks?

yes....all necessary workbooks have been opened. The IT department
won't install the morefuncs addon which would allow me to use
indirect.ext with closed workbooks.

 

[Ron Rosenfeld]

used your formula in the aw13 cell

aw13 = "Orders_June_2006.xls!O_1970_19:Orders_June_2006.xls!O_1970_29"
=SUM(INDIRECT(AW8))
#REF Error
Formula Evaluates to
sum(indirect((""Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29""))

Of course. If you want to use it within a formula, or in a cell that you use
to refer to with the INDIRECT function, there is no need to concatenate double
quotes at the beginning and end.

Given the setup as I specified:

=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))

would evaluate to:

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))

...When I use that formula the result is 10. It should be 100, 10 for
each date cell. So for some reason it is just grabbing the first cell
value?

=SUM(Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29)

...that however returns a value of 100 when used directly




It is a seperate workbook and I have no sheets with that name

The usual way of referring to another workbook is to enclose it in brackets:

[Workbook]sheet1!A1 but I think in certain unambiguous situations, Excel
simplifies the formula.

I'm not certain that you can use INDIRECT to refer to a cell range in another
workbook that is made up of named ranges. At least, I was not able to get that
to work. Maybe someone else knows how.

There is a function: EVAL in Longre's free morefunc.xll add-in that seems to
work under those circumstances.

=SUM(EVAL("[Book3]Sheet2!Code_1:[Book3]Sheet2!Code_2"))

Since you can't use morefunc, you can also write a VBA macro that does
essentially the same thing.

<alt-F11> opens the VB Editor. Ensure your project is highlighted in the
Project Explorer window, then Insert/Module and paste the code below into the
window that opens:

==========================================
Function Eval(str As String)
Eval = Evaluate(str)
End Function
====================================

If it is a workbook, are you aware that INDIRECT cannot be used with closed
workbooks?

yes....all necessary workbooks have been opened. The IT department
won't install the morefuncs addon which would allow me to use
indirect.ext with closed workbooks.



--ron

 

[Grymjack]

Of course. If you want to use it within a formula, or in a cell that you use
to refer to with the INDIRECT function, there is no need to concatenate double
quotes at the beginning and end.

Given the setup as I specified:

=SUM(INDIRECT(AF31&"!O_"&INDEX($A:$A,ROW())&"_"&AL31&":"&AF32&"!O_"&INDEX($A:$A,ROW())&"_"&AL32))

would evaluate to:

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))

OK....some weirdness here. The next to final evaluation step indeed
showed what you say above....

=SUM(INDIRECT("Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29"))

The last step saw the formula evaluate to this......

=SUM([Orders_June_2006.xls]Orders!$U$149)

This cell position is the same position as the named cell of
Orders_June_2006.xls!O_6697_19. Which has a value of 10, which is what
the cell evaluates to. If I type in the following....

=SUM([Orders_June_2006.xls]Orders!O_6697_19):[Orders_June_2006.xls]Orders!O_6697_29)

I get a value of 110, which is correct. As an interesting note after
it showed the proper value the actual typed in formula in the cell
reverted to

=SUM(Orders_June_2006.xls!O_6697_19:Orders_June_2006.xls!O_6697_29)

This happened automatically without any input from me. I tried the
following formula....

=SUM(INDIRECT("["&AF31&"]Orders!O_"&INDEX($A:$A,ROW())&"_"&AL31&":["&AF32&"]Orders!O_"&INDEX($A:$A,ROW())&"_"&AL32))

which evaluated in the next to last step to

=SUM([Orders_June_2006.xls]Orders!O_6697_19):[Orders_June_2006.xls]Orders!O_6697_29)

but then changed to

=SUM([Orders_June_2006.xls]Orders!$U$149)

which has a value of 10.....????

The usual way of referring to another workbook is to enclose it in brackets:

[Workbook]sheet1!A1 but I think in certain unambiguous situations, Excel
simplifies the formula.

I'm not certain that you can use INDIRECT to refer to a cell range in another
workbook that is made up of named ranges. At least, I was not able to get that
to work. Maybe someone else knows how.

That seems to be the major problem. It seems strange that everything
else works but that with the indirect() function.

There is a function: EVAL in Longre's free morefunc.xll add-in that seems to
work under those circumstances.

=SUM(EVAL("[Book3]Sheet2!Code_1:[Book3]Sheet2!Code_2"))

Since you can't use morefunc, you can also write a VBA macro that does
essentially the same thing.

<alt-F11> opens the VB Editor. Ensure your project is highlighted in the
Project Explorer window, then Insert/Module and paste the code below into the
window that opens:

==========================================
Function Eval(str As String)
Eval = Evaluate(str)
End Function
====================================

After adding suggested code to the VB Editor. I typed in the following
formula....

=SUM(EVAL("[Orders_June_2006.xls]Orders!O_6697_29:[Orders_June_2006.xls]Orders!O_6697_29"))

I just get a #NAME error. It doesn't seem to be picking up new function??

 

[Grymjack]

...oppps, sorry. formatted that UDF wrong. I reentered it as......

=SUM(Orders_June_2006.xls!Eval("[Orders_June_2006.xls]Orders!O_6697_29:[Orders_June_2006.xls]Orders!O_6697_29"))

it evaluated to 10....again it looks like it is still pulling the first
value of the sum range......sigh!?!

 

[Grymjack]

Further experimentation. I used the cell function to return both of
the actual cell locations....

A1=CELL("address",Orders_June_2006.xls!O_6697_19)
A2=CELL("address",Orders_June_2006.xls!O_6697_29)

Which resulted in values of....

[Orders_June_2006.xls]Orders!$U$149
[Orders_June_2006.xls]Orders!$AE$149

I then added the values together

=SUM(INDIRECT(A1&":"&A2))


This evaluated to

=SUM(INDIRECT("[Orders_June_2006.xls]Orders!$U$149:[Orders_June_2006.xls]Orders!$AE$149"})

#REF Error


..............ARGHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

 

[Ron Rosenfeld]

==========================================
Function Eval(str As String)
Eval = Evaluate(str)
End Function
====================================

After adding suggested code to the VB Editor. I typed in the following
formula....

=SUM(EVAL("[Orders_June_2006.xls]Orders!O_6697_29:[Orders_June_2006.xls]Orders!O_6697_29"))

I just get a #NAME error. It doesn't seem to be picking up new function??

If the #NAME error is referring to the Eval function, then possibly you do not
have it in a proper module, or you have named some things so as to have an
ambiguity in naming.

Does your project show a section called Modules, with a subsection called
Module1 which contains the code?

Do you have a typo?

Select Insert/Function
User Defined

If you see the function prefixed by a full path name, then you have some naming
ambiguity.

If you don't see the function at all, then you have not placed it in a proper
module.


--ron