Help with creating

[DebbieDoesData]

Hi everyone,
I am new to the forum and while I enjoy using excel (2010), I am still very much a novice. I would truly appreciate some help designing a summary attendance sheet that would pull information from across sheets for each individual class.

Here is what ideally I would need for it to do.
As people register for classes, they would be input into the class roster. Their attendance hours are tracked each individual date for each class and I can find out their total hours for each class.

I would like for there to be a summary sheet, that automatically populates the participants' names into the summary sheet and then tells me the total hours of participation, and finally, would highlight the participants (conditional formatting) once the participant reaches 50 hours and above.

My difficulty is with having the participants listed in the summary sheet because participants are signed up for any number of classes. Some may only be taking one class, while others may be in multiple classes, so I do not want the participants to be listed multiple times, rather listed once, and then having the attendance from each class calculated as their total hours on the summary page.

I may need you to explain it to me like I am a 5 year old, but I would be very grateful for any assistance. Any help is greatly appreciated! Additionally, if anyone has recommendations on these kinds of tutorials, feel free to point me in that direction. I am a novice but am looking to expand my knowledge in any way.

-Debbie

 

[rayk8301]

pivot tables do a good job providing summary of people in the tables, as you input more data, the pivot table can be updated by refreshing the workbook. if you have some sample data to share, we can create a pivot table for you and give you some pointers.

 

[Renaissance]

Hi Debbie,

The Summary tab would be really easy to implement, but I think our best option is to build the Roster tab first (or however you'd like to name it). What type of column headings are you looking at tracking? Would tracking the hours by month work for you? Or do you need individual dates and hours (ie December 15th for 3 hours)?

I'm guessing that someone will be putting these entries in, at least to begin with.

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Dates/Hours
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]January
[/TD]
[TD]February
[/TD]
[TD]March
[/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DebbieDoesData]

Thanks for your replay Rennaisance.... let me see if I can post a sample one.

 

[DebbieDoesData]

Sorry Renaissance, I am unable to post a screen shot. But yes, it would be similar to the one you created above. That would be a sheet for Class 1. Bob may also be taking another class where he has a total across the months of 10 hours, so I would need the summary sheet to fill in Bob and then calculate total class hour. In this case 22, 12 for Class 1 and 10 for Class 2. Does that make sense?

 

[Renaissance]

Hey Debbie,

That's not a problem. Please see the file in the link and let me know if this is somewhere in the ballpark of your vision. And also let me know if the link doesn't work for you (I can always throw it in dropbox, etc.).

https://drive.google.com/file/d/0B66VpIncmi3gLTgzWTNQR3BYcTQ/view?usp=sharing

 

[DebbieDoesData]

Rennaisnace, that is pretty perfect! The only caveat is that Bob and Jennifer would not necessarily take every class, but I think you already accounted fr that. My question is, how would we initially enter participants? Would we enter every participant into the summary and then copy the full roster (for all participants) on each class page? (Of course those not taking the class would just record 0 hours.) Or is there a way to enter separate rosters for each class, and then have the summary sheet pull out the names without duplicates? Another thing I am thinking is, would Bob have to be on row 3 of each sheet in order for it to work? I am just trying to account for people being added to the roster in weeks to come. Thank you so much,you have been incredibly helpful!

 

[DebbieDoesData]

Hi Renaissance, me again. I have been playing more and more with you file and can see that it is really going to fit our needs. I can now tell that it does not matter what row I put a participant in, if they are on different rows it still will total their hours. This is exactly what I needed! Now if I wanted to add a column for last name (so that I could sort by last name), as well as an additional identifier, how would I do that without messing up the formulas? And how would I go about adding additional sheets? Did I mention I am a novice?

 

[Renaissance]

Haha! I'm glad it is a good start to what you want to accomplish. I'd rather teach you how to excel than do everything for you (especially due to you mentioning you want to learn) ^_-

The formulae that are currently in there will not be affected by "appropriate" additions to each sheet. What I mean by that is if you insert an entire column and/or row it will adjust itself accordingly. Conversely, if you selected cells B4:D6 or any sort of combination and right-clicked to "Insert..." -> (then select shift/entire...) it would likely cause errors.

So yes, you can select the column C and right-click on it (the actual C itself) and simply "Insert" then the formulae should not be affected. The same thing goes for inserting rows: Select a row (5) right click on the 5 and insert. Excel will do the rest and automatically add the new row(s) to the formulae (assuming you insert the rows within the ranges).

I setup the basics to make it easy for additional sheets and for renaming. The easiest method for adding sheets would be to right-click on one of the tabs (Hist) and selecting "Move or Copy..." -> Select where you want it to be located and make sure "Create a copy" is checked. After that, rename the tab as you want.

Then, on the Summary tab, left-click the H column and press Ctrl + C, then select the I column and press Ctrl + V. This will copy all the names/formulae/formatting. Then just rename the header exactly the same as the tab and it will do the rest (Please note: Keep the tab names a single word with no spaces, unless you want to learn how to use the Indirect function to reference those as well).

Let me know if that does/doesn't make sense or if you have any further questions :D

 

[DebbieDoesData]

The formulae that are currently in there will not be affected by "appropriate" additions to each sheet. What I mean by that is if you insert an entire column and/or row it will adjust itself accordingly. Conversely, if you selected cells B4:D6 or any sort of combination and right-clicked to "Insert..." -> (then select shift/entire...) it would likely cause errors.

So yes, you can select the column C and right-click on it (the actual C itself) and simply "Insert" then the formulae should not be affected. The same thing goes for inserting rows: Select a row (5) right click on the 5 and insert. Excel will do the rest and automatically add the new row(s) to the formulae (assuming you insert the rows within the ranges).

Let me know if that does/doesn't make sense or if you have any further questions :D

Thanks Renaissance, I was able to add additional sheets based on your instructions perfectly! I will eventually have a file for multiple locations, so the number of classes for each location will vary so this is perfect.

I was still struggling with adding an additional column in the summary and class sheets. I added a column as you described, right-clicking the C and inserting the new column, in both the summary and class sheets. However, the total on the summary sheet now showed "1234" which is the ID number and not the total hours for that individual class. I looked at your formula for the cell and then changed every "D" which is now the ID column, to an "E" which is now the new Totals column, then dragged the fill handle so it would apply to the whole column. It worked! I am sure there was probably an easier way, but I am pretty happy I figured it out on my own!

Thank you again for all your help! I am excited to continue working with it. Thank you, thank you, thank you!