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

E

Excel User Brian

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(January:December!Vacation Bob).
I've also tried =SUM(January:December!Vacation Bob),
=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 yoy,

Brian
 
B

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
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top