Creating a Staff Schedule in Numbers

In B6, the formula is :

=IF(OR(LEN(B6)=0,B6=" "),"",VLOOKUP(B6,Shifts :: Table 1 :: $A:$D,4,0))

in C6, it is :

=IF(C="","",C*INDIRECT("Employees :: "&$A&" :: $A$2"))

Apply fill down

In F6, the formula is :

=IF(OR(LEN(E6)=0,E6=" "),"",VLOOKUP(E6,Shifts :: Table 1 :: $A:$D,4,0))

in G6, it is :

=IF(F="","",F*INDIRECT("Employees :: "&$A&" :: $A$2"))

Apply fill down

…

in B27, it is

=SUM(C)

in B28, it is

=Sum(D)

in E27, it is

=SUM(F)

in E28, it is

=Sum(G

in W6, it is :

=SUM(C6,F6,I6,L6,O6,R6,U6)

fill down.

Yvan KOENIG (VALLAURIS, France) lundi 27 juin 2011 19:46:35

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

I think so, but I can't tell you how without a clearer picture of your second table (what you're calling "the Second page").

At the moment, it sounds like a pretty straightforward application of one of the LOOKUP functions will do the job. Can you post a screen shot of the two tables as you would like them to appear? A simplified version with only a couple of complete rows would be satisfactory.

Regards,

Barry

Why not post a table with fake values ?

It would be easier to build an accurate answer.

Yvan KOENIG (VALLAURIS, France) lundi 27 juin 2011 16:55:01

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Mrogers4 wrote:

For privacy reasons, no. Row 1 is the employee Name, and the second Row is there hourly wage.

I had started on a solution using only the first two tables, but soon realized it would work only if every employee was paid at the same rate.

Without knowing the structure of the third table, there's not enough information to suggest a solution that won't need extensive revision to fit what you've got.

Privacy can be protected by changing the names (make a copy of the table, enter Emp. A in the first name cell, then drag the fill handle to fill the 'names' across the row) and the hourly wage numbers (use 1.00, 2.00, etc.).

Question: The tables shown appear to be from what was originally an MS Excel template. Where can the original be found?

Regards,

Barry

MR,

May we see the table from the Employees Sheet as well?

Jerry