Advanced MS Excel question regarding creating a summary sheet that pulls data from previous sheets in workbook

Question

I am a beginner-intermediate Excel user (good with math, not good with the power of excel). BTW, this is Mac MS Excel.

Scenario:

I have a workbook that tracks daily training stats. Each daily training sheet can be duplicated for as many days the user needs within the month. Each daily training sheet includes an area to track items used for training. Each item listed includes a CHECKBOX to identify whether or not it was used that day.

I want a summary sheet at the end that can provide totals for each item's total usage. It needs to identify checked boxes and ignore unchecked boxes. The catch here is that the number of sheets in the workbook will always change as the user creates new sheets for every day spent training.

For scenario's sake, the name of each sheet is the date of the month (i.e. 01,02,03,04, etc). I cannot just create 31 blank sheets, as some days are spent training multiple times and some days have no training.

How can I create a summary that can do this?

MS Access is not an option. The program needs to be in excel so that everybody can use it (they all have excel, none have access).

Answer 1

Hi,
i would do this all in MS Access and then just use Excel to display the results. hope this helps you.

Answer 2

Yes there is a way. All you have to do is go to the sheet that has the data you want to use when you type in a formula. For example: Say sheet 1 has your data and you want to calculate the sum of data in sheet 1 in sheet 2; Just type =Click on sheet 1, select the data then go back to sheet 2 and press enter.

Answer 3

Hi,

See the resource below. Thanks.

-Jim Gordon
Microsoft Mac MVP

MVPs are independent and do not work for Microsoft
http://mvp.support.microsoft.com/