CODEWITHSUNDEEP
excel
Jez Carter
Jez Carter

Reputation: 11

Updating data from multiple excel worksheets into one

I'm trying to set up an Excel worksheet that automatically updates itself from multiple worksheets, but how do I do it?

Specifically, what I'm trying to achieve is this: I work at a university and I am setting up an attendance register that monitors attendance across different units/modules throughout the year and across different skills groups. I have set multiple worksheets for each of these groups and units/modules which users will enter the data into, and I want this to update one overarching worksheet (no other data will be entered into this - it's just to view the overall picture).

The purpose of this is to establish if, whilst a given student's attendance might be above acceptable levels overall, are there are patterns whereby by they might be skipping specific classes/skills groups etc. Each worksheet has exactly the same set up and design, so I thought it should be straight forward, but it ain't!

Please help - it's ruining my life!

Upvotes: 1

Views: 84

Answers (1)

Kim Sullivan
Kim Sullivan

Reputation: 957

tl;dr; using excel for this is not a good idea, but google "excel table union", use something like https://blog.jamesbayley.com/2013/06/13/how-to-create-a-union-of-n-tables-in-excel/

Firstly - this implementing an attendance register is probably better done using a simple database application instead of a myriad excel sheets (excel is simply not the right tool for the job, that's the reason why it isn't straightfoward). Sooner or later, someone will "break" one of your excel sheets and everything is going to fall apart (also excel can't be realiably edited by several people, so if multiple users want do enter data at the same time, it's not going go work).

You could try to use MS Access if you have a license for it (I'm not a fan of Access, but it's probably still better than using plain excel).

Next thing I would suggest is not using separate worksheets, but use the first column to track the group unit/module.

Use "get and transform" on each of your worksheets, and union them using powerquery

Or lastly, use something that combines different worksheets using indirect references like https://blog.jamesbayley.com/2013/06/13/how-to-create-a-union-of-n-tables-in-excel/

Upvotes: 1

Related Questions