label
 

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 row headings that contain the names of the
employees (cell location has to change from time to time due to changes that
occur in staffing). Columns within the monthly worksheets show the number
of vacation, sick, family leave days, etc. each named employee has used.

I thought that a way to calculate the number of vacation days taken by "Bob"
would be =SUM(January:December!Vacation Bob).
I've also tried =SUM(January:December!"Vacation" "Bob"),
=SUM(January:December! Vacation Bob), and
=SUM(January:December!$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 you,

Brian

label
 

Only a few functions like SUM work with 3D ranges. Certainly implicit
intersection with labels will not, particularly if the labels are in
different locations in each sheet.

One solution would be to insert columns in the various sheets to line up all
the employees across sheets, then use a normal 3D formula.

--
Regards,
Tom Ogilvy


"Excel User Brian" wrote in
message ...
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 row headings that contain the names of the
employees (cell location has to change from time to time due to changes
that
occur in staffing). Columns within the monthly worksheets show the
number
of vacation, sick, family leave days, etc. each named employee has used.

I thought that a way to calculate the number of vacation days taken by
"Bob"
would be =SUM(January:December!Vacation Bob).
I've also tried =SUM(January:December!"Vacation" "Bob"),
=SUM(January:December! Vacation Bob), and
=SUM(January:December!$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 you,

Brian