Assistance with worksheet calculations

**redders** · 02-15-2010, 01:28 AM

Hi again. I am afraid that what I am after is beyond my capabilities so any help would be greatly appreciated.
I have attached a snippet of what I am after. Sheet1 basically is a large list of names and indicates is that person is available on that day or not, a blank cell means they are available.

What I am looking to do on Sheet2 is for each day, to compile a list of people who are available on that day by pulling the data from Sheet1 (sorted order does not matter).

From that list I want to allocate a task to an available person from the "Tasks" list, thus giving me a schedule of tasks for the available people every day.

I am also wanting to have it linked so that if I make someone unavailable from Sheet1, they are then automatically taken out of Sheet2 and their assigned task.

I hope that makes sense, I have no idea how I am best to go about this so as usual I would very much appreciate any assistance you could provide.

Redders.

**JBeaucaire** · 02-15-2010, 01:43 AM

1) On sheet1, go ahead and remove those blank rows at the top.
2) On Sheet2. put this formula in D4 and copy across

=ROWS(Sheet1!C$2:C$7) - COUNTA(Sheet1!C$2:C$7)

3) In D7, put this array formula, be sure to confirm it by pressing CTRL-SHIFT-ENTER:

=IF(ROW(A1)>D$4,"",INDEX(Sheet1!$B$1:$B$7, SMALL(IF(Sheet1!C$1:C$7="",ROW(C$1:C$7),""),ROW(A1))))

If "Barbara" appears, you did it right...now copy that cell down and across the whole table.

No macro used.

**redders** · 02-15-2010, 01:55 AM

Thanks JBeaucaire for your help here but I have done what you have suggested (see attached file) but appears to have done nothing. Not sure where I have gone wrong. Thanks in advance.

**redders** · 02-15-2010, 02:33 AM

I apologise if my explanation of what I was looking to do in Sheet2 was hard to follow. I have attached a copy of the woorkbook where I have completed Sheet2 as I would like it to look but without any formulas etc. I hope that clarifies somewhat.
Again, your assistance is appreciated.

**JBeaucaire** · 02-15-2010, 12:28 PM

Originally Posted by redders

Thanks JBeaucaire for your help here but I have done what you have suggested (see attached file) but appears to have done nothing. Not sure where I have gone wrong. Thanks in advance.

Read the instructions in post #2 again. I said delete blank rows at the top of SHEET1.

On Sheet2, you can put the formulas in D4 as requested, but the dates were originally in D5, so, don't delete rows on Sheet2 unless you're ready to adjust the formulas.

The formula for making names appear was given in post #2 along with CTRL-SHIFT-ENTER instructions, you will need to put that on your sheet as instructed.

Also, I included a sample worksheet in that post, be sure to look at it, you'll see it works..

**redders** · 02-16-2010, 05:10 AM

Sorry, my mistake. Thanks for your help as this is perfect.

**JBeaucaire** · 02-16-2010, 05:12 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)