# Need help with Excel Logic for Student Database

#### mileskh

##### New Member
I am in the process of building a student database on excel which includes 30+ classes and 4 variables per class namely, 1. enrolled date, 2. city, 3. country, and 4. payment method.
As this database will be used by a company that has no employee who can use excel well, I will have to avoid using pivot tables.
They also want a student finder page, where you enter the student's email and it will return all the necessary information for the student including their city and country which they enrolled their last class from as their latest updated city, country, and payment method.

How I have set up right now is to manually enter/copy-paste the data from CSV files for each class into the database using power pivot and link the "finder" page with the database page with vlookup.
My current problem is that when determining each student's latest updated city and country based on the latest enrollment date, I have resulted to using max function for each enrolled date cell and linking it with a very long If function which checks each enrolled date from 31 different classes, which causes a lot of performance problems.

This is how I have set up the database sheet based on their request:
Student Email Student Name Latest Sign Up Date City Country Payment Method | 1. Enrolled Date 1. City 1. Country 1. Payment Method | 2. Enrolled Date 2.City 2. Country 2. Payment

test@gmail.com John Doe "Function 1" "Function 2" "Function 3" "Function 4" | 1-Jan-2021 LA USA BoA | 1-Jan-2022 NYC USA Chase

Function 1 =Max(@1.Enrolled Date, @2. Enrolled Date.....)

Function 2 =IF(@Latest Signup Date = 1. Enrolled Date, 1. City, IF(@Latest Sign up Date = 2. Enrolled Date, 2. City,...)

Function 3 =IF([@[Latest Signup Date]]=[@[1. Enrolled Date]],[@[1. Country]],IF([@[Latest Signup Date]]=[@[2. Enrolled Date]],[@[2. Country]],IF([@[Latest Signup Date]]=[@[3. Enrolled Date]],[@[3. Country]],IF([@[Latest Signup Date]]=[@[4. Enrolled Date]],[@[4. Country]],IF([@[Latest Signup Date]]=[@[5. Enrolled Date]],[@[5. Country]],IF([@[Latest Signup Date]]=[@[6. Enrolled Date]],[@[6. Country]],IF([@[Latest Signup Date]]=[@[7. Enrolled Date]],[@[7. Country]],IF([@[Latest Signup Date]]=[@[8. Enrolled Date]],[@[8. Country]],IF([@[Latest Signup Date]]=[@[9. Enrolled Date]],[@[9. Country]],IF([@[Latest Signup Date]]=[@[10. Enrolled Date]],[@[10. Country]],IF([@[Latest Signup Date]]=[@[11. Enrolled Date]],[@[11. Country]],IF([@[Latest Signup Date]]=[@[12. Enrolled Date]],[@[12. Country]],IF([@[Latest Signup Date]]=[@[13. Enrolled Date]],[@[13. Country]],IF([@[Latest Signup Date]]=[@[14. Enrolled Date]],[@[14. Country]],IF([@[Latest Signup Date]]=[@[15. Enrolled Date]],[@[15. Country]],IF([@[Latest Signup Date]]=[@[16. Enrolled Date]],[@[16. Country]],IF([@[Latest Signup Date]]=[@[17. Enrolled Date]],[@[17. Country]],IF([@[Latest Signup Date]]=[@[18. Enrolled Date]],[@[18. Country]],IF([@[Latest Signup Date]]=[@[19. Enrolled Date]],[@[19. Country]],IF([@[Latest Signup Date]]=[@[20. Enrolled Date]],[@[20. Country]],IF([@[Latest Signup Date]]=[@[21. Enrolled Date]],[@[21. Country]],IF([@[Latest Signup Date]]=[@[22. Enrolled Date]],[@[22. Country]],IF([@[Latest Signup Date]]=[@[23. Enrolled Date]],[@[23. Country]],IF([@[Latest Signup Date]]=[@[24. Enrolled Date]],[@[24. Country]],IF([@[Latest Signup Date]]=[@[25. Enrolled Date]],[@[25. Country]],IF([@[Latest Signup Date]]=[@[26. Enrolled Date]],[@[26. Country]],IF([@[Latest Signup Date]]=[@[27. Enrolled Date]],[@[27. Country]],IF([@[Latest Signup Date]]=[@[28. Enrolled Date]],[@[28. Country]],IF([@[Latest Signup Date]]=[@[29. Enrolled Date]],[@[29. Country]],IF([@[Latest Signup Date]]=[@[30. Enrolled Date]],[@[30. Country]],IF([@[Latest Signup Date]]=[@[31. Enrolled Date]],[@[31. Country]],IF([@[Latest Signup Date]]=[@[32. Enrolled Date]],[@[32. Country]],IF([@[Latest Signup Date]]=[@[33. Enrolled Date]],[@[33. Country]],0)))))))))))))))))))))))))))))))))

Function 4 =IF(@Latest Signup Date = 1. Enrolled Date, 1. Payment Method, IF(@Latest Sign up Date = 2. Enrolled Date, 2. Payment Method,...)

So as you can see Function 1, 2, 3, and 4, needs to be applied to each row (each student) and the database will have over 10k students when completed.

How can I make the whole process more efficient and more performance-friendly for the pc?
Any help would be highly appreciated :'D

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Replies
3
Views
307
Replies
6
Views
310
Replies
7
Views
139
Replies
4
Views
782
Replies
3
Views
269

1,217,760
Messages
6,138,450
Members
450,137
Latest member
HANHAN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back