Can computers handle 1000 sheets in Excel?

[lancewisher]

Hey everyone! New here!

I'm working on a student mental health dashboard project in excel and have a high-level question about adding new 'students' that the school wants to help. Schools can have up to 1000 students, so I don't know if it's reasonable to write VBA code to create a new sheet for every student with their details when the user hits a 'new student' button?

Can Excel handle 1000 sheets / will computers crash / will the file be too big / is this necessary? I want each student to be able to viewed on their own page for ease-of-use (not on a huge matrix all together).

Any ideas on how to add students and make their pages viewable separately without sheets? Also, are there benefits to their details be added in some sort of database like Access? I'm quite new to storing / retrieving data and want to do it in the most efficient way possible with minimal coding (excel VBA / access integration is fine).

Thanks!

 

[Joe4]

Welcome to the Board!

What you are describing is, in fact, a database. As such, a database tool like Access would work much better than Excel. I am not saying that you cannot do it in Excel, but that is not what Excel was really designed for, so it would be a cumbersome process to do in Excel, and preformance might be lagging.

If you do decide to create it in Access, there is more than just learning Access. It is also important to under Relational Database Theory and the Rules of Normalization. The key to a good database is to create normalized data tables, so your data is easy to work with. If they are not set-up correctly, it can make it needlessly difficult to work with the data.

Any ideas on how to add students and make their pages viewable separately without sheets?

So In Access, you would just have a blank form, where you select the User, and it populates it with their data.
In theory, you could probably do the same thing in Excel, with a single sheet that is populated from a bunch of VLOOKUP formulas (that looks up whatever student you select/enter).
So you would have one sheet holding all your data, and one sheet to display detailed information of whichever student you select.

 

[lancewisher]

Thank you!

This is fantastic information. I have spent the last few days learning about databases and Access, and it is exactly what I was looking for.

Very helpful!

 

[Joe4]

You are welcome!
Glad I was able to help.

Note that we also do have a Forum here for Access questions, should you run into any of those down the road.