Excel tables

Resolved · High Priority · Version 2010

Sat 1 Apr 2017, 10:42 Delegate Rhiannon said...

Rhiannon has attended:
Excel Forecasting and Data Analysis course

Excel tables

I have a spreadsheet that tracks the time that myself and colleagues are spending on each project. Each person has a sheet where they enter their projected time, and actual time per project at the end of each month. Each project has a sheet that pulls in data from the people sheets in order to work out how much time we have spent on a project.

We are having to add more and more projects and people to the spreadsheet, which becomes a very time intensive process to update. Every time a new person is added, they have to be added to each project spreadsheet individually. Similarly when a new project is added, I have to update all of the people spreadsheets individually.

Is there any way I can create a master table that would update the rows of all the other tables without interfering with the data inside each of the progeny tables?

Mon 3 Apr 2017, 12:41 Trainer Andrew said...

RE: Excel tables

Hi Rhiannon

thank you for your question. From the background you have given it sounds like the only option to extend your workbook when new project and people are added would be to use some VBA to automate the process.

VBA (Visual Basic for Applications) could be used to get Excel to repeat the steps that you are carrying out manually when your document changes.

Some VBA can be recorded using the built in recorder on the View tab. For more complex processes then it's likely that the sequence will need some programming.

Let me know if this is something you have considered or tried.

Thanks,
Andrew