## I am fairly new to numbers and could use some help regarding linking sheets

459 Views 8 Replies Latest reply: Nov 27, 2012 5:02 AM by Rhuann Joss
Calculating status...
Currently Being Moderated
Nov 19, 2012 5:30 AM

I'm constructing a spreadsheet to show the holidays planned and sick days, taken in a small company (14 staff).  I have 12 sheets - one per month, with each name along the top and the days of the month to the left.  I have managed to do the formulas to show 'Holidays' 'Half Days' and 'Sick'.  I also have 2 additional sheets - one for holiday totals and one for sick totals and would like to know how to link the monthly sheets with the totals sheets at the end.  I'm afraid my knowledge of formulas and functions is very basic, so I would like someone to describe in very simple terms how I should go about this!.  Many thanks

MacBook Pro, Mac OS X (10.6.8)
• Level 6 (11,850 points)

Please post an image of your inputs for one month and what you want the summary sheets to look like so that there are fewer changes once we do post suggestions.

• Calculating status...

Go to a sheet containing summaries.

Double tap on a cell where sums are to be displayed.

Tap on '='. On the right side you will see a small symbol containing a 'Down arrow'.

It's to choose one of your other sheets.

Use SUM to summarise cells from other sheets.

• Level 6 (11,850 points)

Rhuann,

I received an email from so I am going to post the tables you sent with some questions back here.  Is that OK?

• Level 6 (11,850 points)

OK here is the first question:

1) what is supposed to be in the row for "HALF DAY"?

2) what is supposed to be in the row for "FULL DAY"?

3) what is supposed to be in the row for "TOTAL"?

4) just to confirm... the row for "SICK DAYS" is to count the cells in the same column marks with the text "SICK", right?

• Level 6 (11,850 points)

Rhuann,

Here is one possible solution...

I am making the following assumptions:

1)  Each month will have an individual table (for a total of 12 in a year)

2) there will be a summary table for each year

Here is what a monthly table looks like (using the information you provided):

for this table the formulas are:

A4=IF((DATE(\$B\$1, \$B\$2, 1) +(ROW()-3))<= EOMONTH(DATE(\$B\$1, \$B\$2, 1),0), DAY(DATE(\$B\$1, \$B\$2, ROW()-3)), "")

select A4 and fill down to A34 (for a maximum of 31 days in a month)

you enter the year and month in cells B1 and B2

B35=COUNTIF(B4:B34,"*Half Day")

B36=COUNTIF(B4:B34,"*Half Day")

B37=B35/2+B36

B38=COUNTIF(B4:B34, "*sick")

select B35 thru B38 and fill to the right

Now duplicate the whole table by selecting the table then select the menu item "Edit > Duplicate"

update cells B1 and B2 with the year and month (let's assume "2" for "February")

Now the summary table:

B2=IFERROR(INDIRECT("Monthly Data :: "&\$A2&" :: "& CHAR(CODE("B")+(COLUMN()-2))&"38"), "")

select B2 , then fill to the right as needed

then select B2 thru the end of the row (or how ever far you previously filled)

now fill down for twelve months

B14=SUM(B2:B13)

B15  contains 28

B16=B15-B14

select B14 thru B16 and fill to the right

I hope this helps

Regards,

Wayne

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.