Is there a way to use labels and 3-D references in a formala?

[Guest]

Is there a way to use 3-D referencing and labels in the same formula? For
example, I would like to add up the total number of vacation days employees
at my company have taken throughout the year. My excel program has a
worksheet for each month (January to December) and a totals worksheet.
Within each worksheet are the names of the employees (cell location has to
change from time to time due to changes that occur in staffing) that are the
rows. Columns show the number of vacation, sick, family leave days, etc.
they have used.

I thought that a way to calculate the number of vacation days taken by "Bob"
would be =SUM(Januaryecember!Vacation Bob).
I've also tried =SUM(Januaryecember!Vacation Bob),
=SUM(Januaryecember!"Vacation" "Bob"),
=SUM(Januaryecember! Vacation Bob), and
=SUM(Januaryecember!$Vacation Bob) as well as other variations.
But nothing worked (I got the ?Name or !NULL result each time)

Does anyone know anything that might work while using labels?

If not, is there another way that Excel can keep track of the right cells to
sum when the row numbers corresponding to each employee differ across the
monthly worksheets?

Any help you can give will be greatly appreciated.

Thank yoy,

Brian

 

[Biff]

Hi!

Try this:

Create a list of your sheet names somewhere, say, starting in J1:

J1 = January
J2 = February
J3 = March
...
J12 = December

Give this range a defined name, say, WSlist.

Employee names are in column A of the monthly sheets. Vacation days taken
are in column B of the monthly sheets.

You want to count the number of vacation days Bob has taken:

A1 = Bob

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1,INDIRECT("'"&WSlist&"'!B1:B10")))

Biff

 

[Harlan Grove]

Excel User Brian wrote...

Is there a way to use 3-D referencing and labels in the same formula? . . .

No. Labels with spaces between them are intersections of ranges, which
are themselves ranges. 3D references are not ranges per se, so this is
a syntactic restriction.