Data import each month that gets detected and put into larger range

I need help with some formatting of data. This is going to be quite difficult to explain but I shall give enough underlying information to explain what I need to happen in full.

I have a database which produces a sheet of raw data of the invoiced amounts from different areas each month. The person who will be using the spreadsheet is not excel trained to much knowledge and only knows basic skills, i.e copy & paste.

The breakdown of the spreadsheet is;

Workbook 1: Raw data that will be processed every month.

Workbook 2: A huge table with many clients that remains static other than the import of sheet 1’s information each month.

1. What I need to happen is for Workbook 2 to become practical and basically ‘automatic’ in picking up the data from Workbook 1 when it is generated.

I was thinking this could be done by possibly creating some sort of Match or V/Hlookup, or even a macro, on a new sheet in Workbook 2 where the person using the Spreadsheet will just be able to copy and paste into Workbook 2 after it has been generated each month. With these figures then being picked up automatically, I can imagine this getting very complex. The problems may start because the invoiced amounts each month are different clients and some clients may appear missing as this would mean they haven’t been invoiced during that month, as you can see in my sample SS.

I have attached a workbook to this post with Workbook 1 represented as Sheet 1 and the same with Workbook 2 and Sheet 2.

2. Once the above has been done, I then have a table at the beginning of Workbook 2 which needs to pick up the commencement date of the client and their invoiced amounts. This needs to be done on a 5 year turnover basis and needs to pick up the information that is being imported (somehow) each month.

Could you please tell me if this is possible (which is probably is, just to a certain degree of complexity) and have a go at it so I can mirror what has been done?

I shall colour coordinate things on my sample spreadsheet, this can be changed when you are having a go  ..

Thanks, much appreciated.

SAMPLE DATA.xlsx

Hi Aiden
Here is where I am currently with your challenge .
Can you check that the formulae is giving you what you expect before I move to the next stage where we need to protect the last months figures from any changes when the next months data is uploaded.
Tony

Hi Aiden
Please find herewith the latest version for your testing.
NOTE:
The layout on Sheet1 must NOT change, i.e. when you paste the data it MUST start from A1 and the Month and Year MUST be in cell B1.
As soon as you paste each months data there is an automatic macro that runs and pastes the data to the correct column on sheet 2. It then removes the formulae and leaves the values behind so that next months data does not affect it. THIS IS A ONCE ONLY OPTION. So if you make a mistake you will need to copy the formulae from the next column back to the current month and run it again.

Test it out and let me know what you think so far.

Tony

Originally Posted by ARGK

Hi Aiden
Please find herewith the latest version for your testing.
NOTE:
The layout on Sheet1 must NOT change, i.e. when you paste the data it MUST start from A1 and the Month and Year MUST be in cell B1.
As soon as you paste each months data there is an automatic macro that runs and pastes the data to the correct column on sheet 2. It then removes the formulae and leaves the values behind so that next months data does not affect it. THIS IS A ONCE ONLY OPTION. So if you make a mistake you will need to copy the formulae from the next column back to the current month and run it again.

Test it out and let me know what you think so far.

Tony

This looks great and has corrected the error I had with the first attempt!
Please keep me updated! Your efforts are appreciated.

Aiden

Hi Aiden
Please find herewith the latest version with Annual totals included. No email received as yet.
Let me know what you think.
Tony

Hi Tony,

I have sent you an email.. could you please check.. if this hasn't come through could you please confirm your address

This is what was detailed on the email regards the newest update:

The annual turnover varies between the commencement date of each client. For example the
Altrincham client commenced 01/08/2007.
I would then like the totals starting from 1st August 07 to be calculated annually for the next 5 years.
In this example it would mean
year 1 would be "=sum(AA3:AV3)"
But because I want this practically automatically found and calculated it would no doubt be a much more complicated formula/macro for the solution?

.. I would imagine some sort of formula that may need to be created which converts the text string in row 2 into format of MMMM-YY which then extracts the values equal to Column A +365..
I think that is one way of how it could be done? Not quite sure of how to do that though?

If not possibly a macro that uses the commencement month and year from Column A ... finds the correct month&year then sums the next 24 cells in the row as the layout will be remaining the same?

Hope that makes sense!

My email is aidencorcoran@aol.co.uk