Update multiple sets of information automatically from one master set

[sitewolf]

When I first thought of this project, I thought it would be easy. And it is....except for the automatic part, unless I'm missing something (which I probably am, it's Monday and not all neurons are firing).

So I've got a list of children's names, along with their parents' names, phone, email, etc......the distinction between them is they're not all in the same program.
For simplification, say the programs are AM, PM, and All Day.

What I'd LIKE to do is have a master list where ALL are entered, regardless of program. Then I'd like separate tabs for each program where only those in that program display in the list.
Easy, you say- turn each sheet including the master into a matching table, use a formula to pull all the data in, then filter each sheet by the desired program.
Yes, that works...but when I add to the master, the other tabs won't automatically update. Pivot tables would be convoluted- there's 18 columns of info per child (plus they need refreshing).

My desired end result would mean I can update the master and the other sheets update with no further edit, because I'd like to protect these sheets so ONLY I can update/edit, others can only view...but all the info they need autoupdates as I add to the master.

 

[zero269]

When I first thought of this project, I thought it would be easy. And it is....except for the automatic part, unless I'm missing something (which I probably am, it's Monday and not all neurons are firing).

So I've got a list of children's names, along with their parents' names, phone, email, etc......the distinction between them is they're not all in the same program.
For simplification, say the programs are AM, PM, and All Day.

What I'd LIKE to do is have a master list where ALL are entered, regardless of program. Then I'd like separate tabs for each program where only those in that program display in the list.
Easy, you say- turn each sheet including the master into a matching table, use a formula to pull all the data in, then filter each sheet by the desired program.
Yes, that works...but when I add to the master, the other tabs won't automatically update. Pivot tables would be convoluted- there's 18 columns of info per child (plus they need refreshing).

My desired end result would mean I can update the master and the other sheets update with no further edit, because I'd like to protect these sheets so ONLY I can update/edit, others can only view...but all the info they need autoupdates as I add to the master.

Hi Sitewolf,

It sounds like I use something similar to what I think you are describing. I use it for an AR Reading program where I have a Master List of Books, and each Student has books assigned to them and the book data is pulled from the Master List to their own respective Student Sheet.

This would require a Unique ID in the Master List to reference though. In your case, perhaps one for each of the Programs.
Using the XLOOKUP Function

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.microsoft.com

Example of the Master List for Programs:

Book1
	A	B	C	D
1	Program ID	Program Title	Program Description	Program Instructor
2	101	Program 1	Program 1 Description	Program 1 Instructer
3	102	Program 2	Program 2 Description	Program 2 Instructer
4	103	Program 3	Program 3 Description	Program 3 Instructer
5	104	Program 4	Program 4 Description	Program 4 Instructer
6	105	Program 5	Program 5 Description	Program 5 Instructer
7	106	Program 6	Program 6 Description	Program 6 Instructer
8	107	Program 7	Program 7 Description	Program 7 Instructer
9	108	Program 8	Program 8 Description	Program 8 Instructer
10	109	Program 9	Program 9 Description	Program 9 Instructer
11	110	Program 10	Program 10 Description	Program 10 Instructer
Programs

Example of the Student Sheet with their enrolled/assigned programs:

Book1
	A	B	C
4	Program ID	Program Title	Program Instructor
5	102	Program 2	Program 2 Instructer
6	103	Program 3	Program 3 Instructer
7	105	Program 5	Program 5 Instructer
8	111	Not Found	Not Found
Student1

Cell Formulas
Range	Formula
B5:B8	B5	=XLOOKUP([@[Program ID]],Table1[Program ID],Table1[Program Title],"Not Found")
C5:C8	C5	=XLOOKUP([@[Program Title]],Table1[Program Title],Table1[Program Instructor],"Not Found")

You need only enter the Program ID and the rest will automatically populate based on that value.

In the example, I added 111 which is not in the Program Master List which tells you that Program ID was not found. So, either the Program needs to be added to the Master List or the ID number was entered incorrectly.

You'd want to create a Master List for each student as well so you can do the same thing.... although I'm not sure you really need a Sheet for each Student...

To avoid that, you can use Data Validation (drop-down list) for the Program ID column for each student and use the Program ID column on the Master List your List of data to populate a drop-down list.

Create a drop-down list - Microsoft Support

You can insert a drop-down list of valid entries (also called a drop-down menu or drop-down box) in Excel to make data entry easier, or to limit entries to certain items that you define.

support.microsoft.com

 

[zero269]

If using a Student master list and a single sheet to track active enrollments... perhaps something similar to this...

Book1
	A	B	C	D	E	F
1	Student ID	Student Name	Parent 1	Parent 2	Phone 1	Phone 2
2	101	Yurem Duran	Angeline Duran	Jeffrey Duran	890-772-4446
3	102	Roderick Willis	Gracie Willis	Gabriel Willis	938-644-1756	797-442-9848
4	103	Scarlet Marquez	Miriam Marquez	Rex Marquez	539-319-8244	978-607-8117
5	104	Carla Lawson	Kayden Lawson	Ayanna Lawson	725-930-2335
6	105	Ella Russo	Alden Russo	Mary Russo	573-793-1855	224-706-2491
7	106	Carlie Mcpherson	Gunnar Mcpherson	Messiah Mcpherson	468-366-6218
8	107	Lennon Paul	Kassandra Paul	Logan Paul	579-691-8355	998-834-5254
9	108	Emmy Koch	Julio Koch		785-272-5774	322-903-7310
10	109	Jaeden Oliver	Wilson Oliver		395-897-4377
11	110	Messiah Salas	Luciana Salas	Saul Salas	998-834-5254
Students

Below you can see that some students are enrolled in more than one program...

Book1
	A	B	C	D	E
1	Student ID	Student Name	Program ID	Program Title	Program Instructor
2	101	Yurem Duran	101	Program 1	Program 1 Instructer
3	101	Yurem Duran	101	Program 1	Program 1 Instructer
4	102	Roderick Willis	102	Program 2	Program 2 Instructer
5	102	Roderick Willis	102	Program 2	Program 2 Instructer
6	103	Scarlet Marquez	103	Program 3	Program 3 Instructer
7	103	Scarlet Marquez	103	Program 3	Program 3 Instructer
8	104	Carla Lawson	104	Program 4	Program 4 Instructer
9	105	Ella Russo	105	Program 5	Program 5 Instructer
10	105	Ella Russo	105	Program 5	Program 5 Instructer
11	106	Carlie Mcpherson	106	Program 6	Program 6 Instructer
12	106	Carlie Mcpherson	107	Program 7	Program 7 Instructer
13	107	Lennon Paul	107	Program 7	Program 7 Instructer
14	108	Emmy Koch	108	Program 8	Program 8 Instructer
15	109	Jaeden Oliver	109	Program 9	Program 9 Instructer
16	109	Jaeden Oliver	109	Program 9	Program 9 Instructer
17	110	Messiah Salas	109	Program 9	Program 9 Instructer
18	110	Messiah Salas	110	Program 10	Program 10 Instructer
19	Total	17		17	17
Active Roster

Cell Formulas
Range	Formula
D2:D18	D2	=XLOOKUP([@[Program ID]],Table1[Program ID],Table1[Program Title],"Not Found")
E2:E18	E2	=XLOOKUP([@[Program Title]],Table1[Program Title],Table1[Program Instructor],"Not Found")
D19	D19	=SUBTOTAL(103,[Program Title])
E19	E19	=SUBTOTAL(103,[Program Instructor])
B2:B18	B2	=XLOOKUP([@[Student ID]],Table14[Student ID],Table14[Student Name])
B19	B19	=SUBTOTAL(103,[Student Name])

 

[zero269]

You'd want to create a Master List for each student

Correction... a single Master List for all Students...