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