Using Name as Worksheet Reference

**malik641** · 09-04-2005, 02:54 PM

I have a defined name that holds the names of each relevant worksheet I want to calculate.

Lets Say I want to sum every A1 in each worksheet in the defined name. How would I go about doing that?

Bruno Campanini · 09-04-2005, 04:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-04-2005, 05:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-04-2005, 05:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

**malik641** · 09-04-2005, 10:54 PM

Originally Posted by Biff

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

I'm getting a #Name? error with this. I checked it out and the error comes from this:

""&SheetNames&"

I have NO clue how this worksheet function is supposed to work. Especially the N before the INDIRECT function. Can you explain that a little bit??

Thanks

Peo Sjoblom · 09-05-2005, 12:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-05-2005, 12:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

**malik641** · 09-05-2005, 12:57 AM

Awesome, got it!

Now I need to figure out how to use this with certain criteria...hmmmm

But anyway thanks again Biff and Peo.
Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA Function. Guess I should have specified. Sorry

Harlan Grove · 09-05-2005, 01:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-05-2005, 02:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-05-2005, 10:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-05-2005, 10:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-05-2005, 10:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-05-2005, 10:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-05-2005, 10:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-05-2005, 10:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-05-2005, 10:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-05-2005, 11:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-05-2005, 11:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-05-2005, 11:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-05-2005, 11:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-05-2005, 11:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-05-2005, 11:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-05-2005, 11:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 12:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 12:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 12:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 12:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 12:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 12:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 12:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 01:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 01:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 01:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 01:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 01:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 01:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 01:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 02:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 02:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 02:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 02:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 02:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 02:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 02:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 03:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 03:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 03:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 03:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 03:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 03:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 03:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 04:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 04:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 04:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 04:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 04:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 04:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 04:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 05:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 05:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 05:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 05:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 05:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 05:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 05:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 06:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 06:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 06:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 06:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 06:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 06:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 06:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 07:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 07:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 07:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 07:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 07:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 07:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 07:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 08:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 08:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 08:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 08:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 08:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 08:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 08:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 09:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 09:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 09:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 09:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 09:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 09:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 09:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

**malik641** · 09-06-2005, 09:37 AM

Originally Posted by Biff

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Two things:
1st Off, what the hell's wrong with this thread? Why is it repeating itself a rediculous amount of times?????

2nd, The SUMIF function is not working for me. I'm looking for something along the lines of:

Sheet1
C1:2 D1:Yes

Sheet2
C1:2 D1:Yes

Sheet3
C1:2 D1:No

Here is the formula that I have to add every C1 in worksheets from Range "Employees" (Defined Name):

=SUMPRODUCT(N(INDIRECT("'"&Employees&"'!C1")))

How can I sum the worksheets from Range "Employees"C1 where D1=Yes???

Bruno Campanini · 09-06-2005, 10:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 10:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 10:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 10:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 10:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 10:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 10:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

**malik641** · 09-06-2005, 10:15 AM

Okay, I figured that part out.

=SUMPRODUCT((T(INDIRECT("'"&Employees&"'!D1"))="Yes")*(N(INDIRECT("'"&Employees&"'!C1"))))

NOW what my REAL criteria is:
B2=1-Jan
A4=Hitachi 717

Formula is in B4.

What I'm looking for from this is to sum the values in each "Employees" sheet in the column that equals 1-Jan (from B2) and in the row that equals "Hitachi 717".

Here's what I came up with, but it's not working.

=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))

In every "Employees" sheet in row 1:1 is the Date value. And in Column A:A in every "Employees" sheet is where "Hitachi 717" would be found. Where these two will intersect is what I want to sum.

Any ideas??

Bruno Campanini · 09-06-2005, 11:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 11:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 11:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 11:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 11:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 11:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 11:05 AM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 12:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 12:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 12:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 12:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 12:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 12:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 12:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 02:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 02:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 02:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 02:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 02:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 02:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 02:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 03:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 03:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 03:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 03:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 03:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 03:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 03:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 04:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 04:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 04:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 04:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 04:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 04:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 04:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Bruno Campanini · 09-06-2005, 05:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 05:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 05:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 05:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 05:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 05:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 05:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 05:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 06:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 06:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 06:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 06:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 06:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 06:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 06:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 06:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 07:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 07:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 07:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 07:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 07:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 07:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 07:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 07:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 08:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 08:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 08:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 08:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 08:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 08:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 08:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 08:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 09:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 09:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 09:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 09:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 09:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 09:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 09:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 09:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 10:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 10:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 10:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 10:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 10:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 10:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 10:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 10:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-06-2005, 11:05 PM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-06-2005, 11:05 PM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-06-2005, 11:05 PM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-06-2005, 11:05 PM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-06-2005, 11:05 PM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 11:05 PM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Biff · 09-06-2005, 11:05 PM

Hi!

> Now I need to figure out how to use this with certain criteria

Depends on what you want to do.

You might be able to use something like:

=SUMPRODUCT(SUMIF(..................................

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Awesome, got it!
>
> Now I need to figure out how to use this with certain criteria...hmmmm
>

>
>
>
> But anyway thanks again Biff and Peo.
> Bruno, thanks for the VBA but I kinda wanted to avoid using a VBA
> Function. Guess I should have specified. Sorry
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-06-2005, 11:05 PM

malik641 wrote...
....
>NOW what my REAL criteria is:
>B2=1-Jan
>A4=Hitachi 717
>
>Formula is in B4.
>
>What I'm looking for from this is to sum the values in each "Employees"
>sheet in the column that equals 1-Jan (from B2) and in the row that
>equals "Hitachi 717".
>
>Here's what I came up with, but it's not working.
>
>=SUMPRODUCT((N(INDIRECT("'"&Employees&"'!$1:$1))=B$2)*(T(INDIRECT("'"&Employees&"'!$A:$A"))=$A4))
>
>In every "Employees" sheet in row 1:1 is the Date value. And in Column
>A:A in every "Employees" sheet is where "Hitachi 717" would be found.
>Where these two will intersect is what I want to sum.
>
>Any ideas??

You can't do this if your A4 value could be anywhere in col A in the
other worksheets *AND* your B2 value could be anywhere in row 1 in the
other worksheets. If that were the case, you'd need a 3D array or some
means of isolating the appropriate column in each worksheet separately.
Neither are possible.

However, if all the employee worksheets would have the same row 1, so
1-Jan would be in the same column in every employee worksheet, you
could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&Employees&"'!A:A"),$A4,
INDIRECT("'"&Employees&"'!C"&
MATCH(B$2,INDIRECT("'"&INDEX(Employees,1)&"'!1:1"),0),0)))

Bruno Campanini · 09-07-2005, 12:05 AM

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.

Do you mean a Collection of worksheets you want to calculate?

-------------------------------------
Dim i, SumOfA1Cells as Currency
For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i).[a1])
Next
-------------------------------------

> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?

Or, for any worksheet in active workbook:
--------------------------------------------
Dim i, SumOfA1Cells As Currency
For Each i In ThisWorkbook.Worksheets
SumOfA1Cells = SumOfA1Cells + Sheets(i.Name).[a1]
Next
--------------------------------------------

Bruno

Bruno Campanini · 09-07-2005, 12:05 AM

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...

Ooops!

That's better:

For Each i In NameOfSheets
SumOfA1Cells = SumOfA1Cells + Val(Sheets(i.Name).[a1])
Next

Bruno

Biff · 09-07-2005, 12:05 AM

Hi!

Try this:

J1 = Sheet1
J2 = Sheet3
J3 = Sheet10

J1:J3 is given the defined name SheetNames

=SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))

This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> I have a defined name that holds the names of each relevant worksheet I
> want to calculate.
>
> Lets Say I want to sum every A1 in each worksheet in the defined name.
> How would I go about doing that?
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Peo Sjoblom · 09-07-2005, 12:05 AM

You need to give the range a name (insert>name>define) as per your
instructions

"J1:J3 is given the defined name SheetNames"

or use the range as in

=SUMPRODUCT(N(INDIRECT("'"&J1:J3&"'!A1")))

--
Regards,

Peo Sjoblom

(No private emails please)

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Biff · 09-07-2005, 12:05 AM

Hi!

Peo explained the #NAME? problem.

As far as N goes.......

I don't know the exact reason it's needed. It's one of those quirky type
things you just run into.

It usually comes into play when you're trying to deal with 3D references.
For some reason if you just used:

=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!A1"))

The arguments to INDIRECT will return #VALUE! errors. Using N (in this case)
causes the arguments to actually return their true values. The N() function
is used for numeric values and the T() function is used for text values.

If you only wanted to sum the same cell on 3 different sheets I would just
use:

=SUM(Sheet1!A1,Sheet3!A1,Sheet10!A1)

The SUMPRODUCT method is good if you have many sheets to sum.

Biff

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> J1 = Sheet1
>> J2 = Sheet3
>> J3 = Sheet10
>>
>> J1:J3 is given the defined name SheetNames
>>
>> =SUMPRODUCT(N(INDIRECT("'"&SheetNames&"'!A1")))
>>
>> This will SUM Sheet1!A1, Sheet3!A1, Sheet10!A1
>>
>> Biff
> I'm getting a #Name? error with this. I checked it out and the error
> comes from this:
>
> ""&SheetNames&"
>
> I have NO clue how this worksheet function is supposed to work.
> Especially the N before the INDIRECT function. Can you explain that a
> little bit??
>
> Thanks
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=401807
>

Harlan Grove · 09-07-2005, 12:05 AM

"Biff" wrote...
....
>As far as N goes.......
>
>I don't know the exact reason it's needed. It's one of those
>quirky type things you just run into.
>
>It usually comes into play when you're trying to deal with
>3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.

Using Name as Worksheet Reference

LinkBack

Thread Tools

Rate This Thread

Display

Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference

Re: Using Name as Worksheet Reference