Hello experts! I have a new challenge. As I've said in the past, I'm not an
Excel expert, but I know enough to be dangereous (or screw things up;
depending on how you look at things)
I'm working on a crew schedule that is a 24/7 work schedule. The actual
schedule is on one worksheet, but I need to count crew shifts for each person
(they are 'rewarded' after working X amount of shifts.) The problem, is
these people are often moved around the worksheet from "Alpha" crew to
"Bravo" crew and don't necessarily stay in the same row every month. Each
schedule is worked monthly, and the # of shifts worked are added up on the
main page.
I'd like to add another worksheet, and add the current month's shifts to the
amount of shifts previously. I'd then copy the shift count worksheets to the
NEXT month's schedule.
In other words, what I have for the Aug Schedule:
In C7 I have "Bob", then in D7-AG7 I have his schedule, and AI7 is his
monthly shifts total
What I want to do in my added worksheet is create a formula that says if the
COLUMN C has "Bob", then take the number of shifts in the applicable cell in
Column AI (because Bob won't always be in row 7)
Does that make sense? I'm trying to create a running talley of shifts
worked each month and keep adding them together.
If these people would stay on the same crew I'd have it licked (darn it)

Please help me look "smart" :-)
Thanks

The best way to search a name in a list is to use the functions MATCH or
VLOOKUP. MATCH returns the row of the first found name, while VLOOKUP returns
the value on the same row and on the nth column of the range.

So in your case, I would use VLOOKUP("Bob",C2:AI207,33,0). 33 is the 33th
column from C, which means AI. Adapt the range length C2:AI207 to your needs.
You can also replace "Bob" by a cell reference, of course. And last comment,
the final 0 means that the range is unsorted, slower but it may be better
adapted to your needs.

Stephane.

--
Message posted via http://www.officekb.com