Using Name as Worksheet Reference

M

malik641

I have a defined name that holds the names of each relevant worksheet
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
 
B

Bruno Campanini

malik641 said:
I have a defined name that holds the names of each relevant worksheet I
want to calculate.
Click to expand...

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?
Click to expand...

Or, for any worksheet in active workbook:
 
B

Bruno Campanini

Ooops!

That's better:

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

Bruno
 
B

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
 
M

malik641

Biff said:
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
Click to expand...
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
 
P

Peo Sjoblom

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)
 
B

Biff

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
 
H

Harlan Grove

...
....
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.
Click to expand...
....

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

malik641

Awesome, got it!

Now I need to figure out how to use this with certain criteria...hmmm
:confused:



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

Biff

Hi!
Now I need to figure out how to use this with certain criteria
Click to expand...

Depends on what you want to do.

You might be able to use something like:

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

Biff
 
M

malik641

Biff said:
Hi!


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]...
Click to expand...
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???
 
M

malik641

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 tha
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 Colum
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?
 
H

Harlan Grove

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??
Click to expand...

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)))
 
Top