Can you update a spreadsheet automatically w/info from multiple spreadsheets in workbook?

[wadee]

Hello,

This is my first time posting, so I will try to give as much info as possible, but please let me know if I need to give more.

I have a workbook with 17 spreadsheets in it. The first tab holds all the information from all of my other tabs, and the remaining tabs are for 16 different projects that I am managing.

The project tabs have 11 columns of data that will be listed. I have data for the current year and projected data for the next year split into 2 tables under the 11 column headers. At the bottom of the each table I have a row which sums the totals of the expense columns i.e. =sum(F7:F10) and =sum(F14:F20). And at the bottom of the worksheet I have a row which sums the totals of expenses for each coulmn i.e. =sum(F11+F21).

My "Summary Tab" shows all the data from the other worsheets all together. Although, I know this seems a bit repeatitive, this is how it has to be done. What I would like to do is have my summary tab automatically fill in with the data from the other 16 tabs any time the information on those sheets is changed/added/removed.

Please note that there may be times when rows will have to be added to the tables as the current set up I have may not have enough sapce for all the initiatives that need to be included on the spreadsheet (this is why I created the tables, so that when a new row was inserted, the formulas and formatting would automatically insert in the new row without have to copy and paste).

Also, please note that several people will be updating this workbook, so I want it to function automatically so that other users will not have trouble updating their information. I may need to add additional tabs as well and will need to know how to include news tabs into the automatic update.

Does anyone know if there is a way to do this?

Forum Help.xlsx
I've included a copy of my spreadsheet, althoguh I have removed the data and just inserted some sample info.

Thank you in advance for any help!!

[aydeegee]

Hi wadee

Welcome to the forum.

As I understand it, the summary sheet could potentially have 16 x 7 rows in the Carryover section and 16 x 11 rows in the 2014 section (with the 16 increasingas requires). Presumably the 2014 could be even more rows for some projects?

The low tech way would be just to have in Summary A7 the formula =AB!A8 . Copy and paste this down for all the 2013 rows in AB across 11 columns. Repeat for 2014. repeat for all projects

This would only have to be updated if additional rows or additional projects were undertaken. Other users might be tempted to "help" which migh take some time to discover.

The macro way would be: clicking a button which would collect all data from all sheets and display it in the Summary sheet. This would automatically adjust for new rows and new projects. Other users would not be tempted to "help".

Let me know if you would like the macro version - I would need to know the maximum rows that could possibly used in ang given project.

Regards
Alastair

[wadee]

Hi Alastair,

Thank you for your response. On the sumarry tab I am currently compiling data from 17 other worksheets, however the number of worksheets will increase in the future as we aquire/develop more sites. The Carryover section could have as many as 20 rows per worksheet (I don't think it would ever be more than 20, but I suppose it is possible). The 2014 (or current year) section could also have as many as 20 rows (again, I don't think it would be more than that, but it could become possible).

Currently, I have the spreadsheet set up with minimum number of rows per section, but I also need to be able to insert more rows as needed and have the inserted row keep the smae formatting and equations which is why I made the two sections tables. This was the only way I knew how to achieve auto formatting/equations when inserting a row.

I think the macro might work better, so that (as you put it) "others wouldn't be tempted to help". Is there a macro that I could do that will allow me to add more worksheets and more rows to the exsisting worksheets and still update the summary tab as I need it to be?

Thank you in advance for you time and help!

Best Regards,
WAdee

[aydeegee]

Hi Wadee

Here is my solution. It can be run wither by clicking "Update from Projects", or if you want to delete that shape, Ctrl+shift+Q will run the VBA.

I am egotistical enough to hope that this will be used for future years, so have highlighted the cells that the macro relies on. Change these cells and the macro will not run, until the macro is changed. To see the code use Alt+F8 / Step into. Feel free to remove the highlighting!

You may need to change your security setting in order for the macro to run.

Let me know if you want anything explaining.

Regards
Alastair