Using Name as Worksheet Reference

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

Originally Posted by malik641

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?

Ithink you could use this function to do that (3D SUM)

=SUMA(Sheet1:Sheet3!A1)

Saludos

César