Hello,
Wondering if anyone can help me.
I've been asked to create a databse using Excel 2010 for a friend who runs three choirs. He currently stores information about each choir member in 3 separate spreadsheets. He also prints out attendee names to sign a register each week which is all kept in paper format. He now needs to centralise this data to make it easier for him to manage.
I need to create 1 spreadsheet with the following:
TAB 1: Holds the core dataset for each member of all 3 Choirs (e.g. Columns for Name, still Active, Choir1/Choir2/Choir3, contact details etc.)
TAB 2 for Choir1 - using data from TAB1, I want to display all Active members of Choir 1 in one column. Then I want to use these names to create an Attendence sheet, with subsequent columns with heading dates and a 1 or 0 next to each name depending on whether they attended that particular session.
TAB3: Choir 2, same as above
TAB 3: Choir 3, same as above
I have prepared the data for TAB 1. Every member of all 3 Choirs have their details stored on one row of an Excel table.
I had planned on using this data to create a Pivot table which would allow me to filter for the specific Choir and whether or not they are an Active member. So far so good.
The problem I have is that I now want to extend the Pivot table to include new columns headed with weekly dates which can be edited each week when the register is taken.
I have found this doesn't work - if someone were to leave (not Active) and the Pivot table refreshed then the person would be removed from the list but the new data manually added will not be affected.
Essentially I want to extend the Pivot table but without data from the initial data set.
Does anyone have any idea how I can do this so that people's details are only entered once in the data set and the subsequent Attendence sheets are auto populated with the name data?
Hope this makes sense!
Many thanks in advance.
Wondering if anyone can help me.
I've been asked to create a databse using Excel 2010 for a friend who runs three choirs. He currently stores information about each choir member in 3 separate spreadsheets. He also prints out attendee names to sign a register each week which is all kept in paper format. He now needs to centralise this data to make it easier for him to manage.
I need to create 1 spreadsheet with the following:
TAB 1: Holds the core dataset for each member of all 3 Choirs (e.g. Columns for Name, still Active, Choir1/Choir2/Choir3, contact details etc.)
TAB 2 for Choir1 - using data from TAB1, I want to display all Active members of Choir 1 in one column. Then I want to use these names to create an Attendence sheet, with subsequent columns with heading dates and a 1 or 0 next to each name depending on whether they attended that particular session.
TAB3: Choir 2, same as above
TAB 3: Choir 3, same as above
I have prepared the data for TAB 1. Every member of all 3 Choirs have their details stored on one row of an Excel table.
I had planned on using this data to create a Pivot table which would allow me to filter for the specific Choir and whether or not they are an Active member. So far so good.
The problem I have is that I now want to extend the Pivot table to include new columns headed with weekly dates which can be edited each week when the register is taken.
I have found this doesn't work - if someone were to leave (not Active) and the Pivot table refreshed then the person would be removed from the list but the new data manually added will not be affected.
Essentially I want to extend the Pivot table but without data from the initial data set.
Does anyone have any idea how I can do this so that people's details are only entered once in the data set and the subsequent Attendence sheets are auto populated with the name data?
Hope this makes sense!
Many thanks in advance.
