Registration tracking sheet

[JPL]

Hi there, new to the site and I really need some help. I've tried by my self (and with online resources) with no luck.

I have a file called online class registration. The first sheet is called DATA and, every Monday, I will paste into it data from a static excel report from a MS Form. The data includes registration information from people looking for English classes. I have a second sheet called registration tracking in which I have used a formula to copy key information from the data sheet, such as Name (Column A), Email (Column B), Phone number (Column C) and level of English (Column D). Column E is called Assignment, in which I have set up a drop down menu through data validation. The options are ‘Waiting list’, ‘Beginner’, Intermediate’, ‘Advanced’ and ‘Deregistered’. For each of the menu options, there is a corresponding sheet with the same name.

What I want to happen is, when I select an option from the drop down menu in the ‘Registration tracking’ sheet column E (e.g. ‘Waiting list’) , I want the data from Columns A-D on the same sheet to be duplicated in the next empty row in the corresponding sheet. So for example, when I select ‘Waiting List’ in E2 of Registration tracking, the data in A2-D2 will appear in A2-D2 of the Waiting List sheet, whilst also remaining unchanged in ‘Registration Tracking’. If I then select ‘Waiting List’ in E10 of Registration tracking, the data in A10-D10 will appear in A3-D3 of the Waiting List sheet (assuming they are the next available empty cells).

I want the data to stay in the registration tracking sheet after I select an option from the drop down menu. If I then change the drop down menu from ‘Waiting list’ to ‘Beginner’, I want their data to appear in the beginner sheet, remain in the registration tracking, and also be removed from the waiting list. All existing data below the deleted data in ‘Waiting list’ will then move up one row to remove the empty row.

Thank you in advance to anyonw who answers

 

[KRice]

As you've suggested in your posting, a VBA solution could do this, while a formula based approach would be problematic, as it wouldn't be able to remove a re-classified entry from an existing worksheet. But have you considered not using separate worksheets for each of the column E items? Since you want a current consolidated list for each column E item, you could turn on autofilter and use the column heading filter to create the consolidated list directly from your class registration worksheet...no additional worksheets, formulas, or VBA code are necessary.

Book5
	A	B	C	D	E
1	Name	Email	Phone	Level of English	Assignment
2	name1	email1	phone1	0	Beginner
3	name2	email2	phone2	1	Intermediate
4	name3	email3	phone3	1	Intermediate
5	name4	email4	phone4	2	Advanced
6	name5	email5	phone5	1	Beginner
7	name6	email6	phone6	3	Advanced
8	name7	email7	phone7	1	Deregistered
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
E2:E8	List	Waiting list,Beginner,Intermediate,Advanced,Deregistered

with Beginner chosen in filter heading:

Book5
	A	B	C	D	E
1	Name	Email	Phone	Level of English	Assignment
2	name1	email1	phone1	0	Beginner
6	name5	email5	phone5	1	Beginner
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
E2:E8	List	Waiting list,Beginner,Intermediate,Advanced,Deregistered

 

[JPL]

As you've suggested in your posting, a VBA solution could do this, while a formula based approach would be problematic, as it wouldn't be able to remove a re-classified entry from an existing worksheet. But have you considered not using separate worksheets for each of the column E items? Since you want a current consolidated list for each column E item, you could turn on autofilter and use the column heading filter to create the consolidated list directly from your class registration worksheet...no additional worksheets, formulas, or VBA code are necessary.

Book5
	A	B	C	D	E
1	Name	Email	Phone	Level of English	Assignment
2	name1	email1	phone1	0	Beginner
3	name2	email2	phone2	1	Intermediate
4	name3	email3	phone3	1	Intermediate
5	name4	email4	phone4	2	Advanced
6	name5	email5	phone5	1	Beginner
7	name6	email6	phone6	3	Advanced
8	name7	email7	phone7	1	Deregistered
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
E2:E8	List	Waiting list,Beginner,Intermediate,Advanced,Deregistered

with Beginner chosen in filter heading:

Book5
	A	B	C	D	E
1	Name	Email	Phone	Level of English	Assignment
2	name1	email1	phone1	0	Beginner
6	name5	email5	phone5	1	Beginner
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
E2:E8	List	Waiting list,Beginner,Intermediate,Advanced,Deregistered

I can't thank you enough for taking the time to reply.

Using filters is my back up plan, I really appreciate your detail on this option!

I think this is the best option, even though I would love to have, and understand the VBA functionality, for the purposes of time I will take your advice.

Thank you again, what a super community here!