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" wrote in
message ...

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" wrote in message
...

Ooops!

That's better:

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

Bruno

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" wrote in
message ...

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

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

You need to give the range a name (insertnamedefine) 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" wrote in
message ...

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

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" wrote in
message ...

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" 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.

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

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" wrote in
message ...

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