Need some help with automation and process.

[thesoupdragon]

Hi,

First post here so not sure whether this is in the right place or not. If not the apologies.

I haven't used Excel for over a decade (or any other Microsoft product) so I'm looking for some help on an approach to improving a process. Here's the situation:

1. Every month in the financial year (Apr to Mar) a report is produced from another system and stored in an excel spreadsheet.
2. User makes necessary data mods (nothing formula-related) to the spreadsheet each month, workbook is saved as Apr or May or Jun or whatever the current month is.
3. As user has never had any Excel training, the user then manually extracts data from the current w/book summarises it as required and manually types it into another 'Summary' w/book
4. At the end of the financial year there are 12 monthly w/books and one Summary w/book

I've been asked to 'streamline' the process and I want to automate it as much as possible. As I haven't used Excel for many years I'm a bit rusty and I think my first solution will work but is a bit clunky and there must be a more elegant solution. Anyway, here's my first, clunky go at it:

1. I create a Master w/book with a tab for each month (Apr, May, Jun etc)
2. Within each tab I add the formulas to enable the necessary summarisation
3. Create a Summary tab in the Master w/book which can summarise each of the monthly tabs
4. Every month the user copies and pastes the data from the monthly w/book into the corresponding tab in the Master w/book
5. At the end of every financial year the user has to clear out all the data in each of the monthly tabs

So there it is. I think it will work but I think there must be a more elegant solution.

Anyone got any ideas on how to make it more automated and more elegant?

Thanks in advance.

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[davsth]

If you are consistent with your naming, you could have a macro to update the data in our sheet, and potentially perform the data modifications, depending what they are


The main thing I would do is put all the data into the same sheet, not a sperate tab for each month. then you can use the same formula for the results all the time, you just need to have a column to differentiate the months.

Formula or a pivot table may give you what you needs.

You could clear the data at the end of the year, although keeping it would allow comparison with the previous year.

Its hard to be more specific without seeing the data

[thesoupdragon]

If you are consistent with your naming, you could have a macro to update the data in our sheet, and potentially perform the data modifications, depending what they are


The main thing I would do is put all the data into the same sheet, not a sperate tab for each month. then you can use the same formula for the results all the time, you just need to have a column to differentiate the months.

Formula or a pivot table may give you what you needs.

You could clear the data at the end of the year, although keeping it would allow comparison with the previous year.

Its hard to be more specific without seeing the data

Thanks for replying ...

I could use a macro to add the necessary formulas but the mods need to be done by the user ...

I had considered using a macro to load the monthly data into a single sheet in the Master w/book but that discounted it due to my perception of the problems around making sure the 'new' data being loaded doesn't overwrite the 'existing' data in the sheet. Have I missed an easy way of overcoming that issue?

I suppose what I mean is ... e.g. I already have Apr data loaded into the same sheet in the Master w/book, how would I make sure that the May data didn't overwrite the existing data? Something to do with last used row??

EDIT .. and understanding the mechanics of which of the monthly sheets to be loaded.

[davsth]

You can write a macro to post the bottom of the data, and check if the month has already been imported, so you don't post more than once! Without a sample answers will be very general
https://www.mrexcel.com/forum/excel-...tom-table.html
https://www.mrexcel.com/forum/excel-...her-sheet.html

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter the thread and make it hard to read.
Use the "Quick reply" instead
Thanks

[thesoupdragon]

Thanks, those links look helpful, I'll check them out.