Collating data into a consolidated view

[francri]

Hi All,

I am very new to excel and VBA, I can record a macro and then go to edit and generally get a good feel for the syntax, but I am coming unstuck very quickly...

I have a report in excel which is delivered from 4 people on a weekly basis. the report shows number of requirements open + various data on one worksheet (Open), and number of placements made + various other data on another worksheet (Placed).

What I need to do is collate all of this data into one on a weekly basis, then create a monthly consolidated that will show how many requirements were open in the month, how many palcements were made etc.

I have attached an example spreadsheet. The weekly report are the first 2 worksheets then the monthly summary is the rest.

I wonder if anyone is able to point me in the right direction with this.

thanks in anticipation

[mdmackillop]

Welcome to VBAX
Do you mean that you receive a 4 workbooks each with 2 sheets every week?
Can you submit a sample report and show in your summary how this data gets consolidated. Simply list every entry or total by day/week etc. in each line?

[francri]

Welcome to VBAX
Do you mean that you receive a 4 workbooks each with 2 sheets every week?
Can you submit a sample report and show in your summary how this data gets consolidated. Simply list every entry or total by day/week etc. in each line?

Hi MD, Yes I receive 4 seperate reports every week, each one containing 2 worksheets (Open & Placed). I will populate with dummy data and also upload an example of how it needs to be collated.

In the meantime, on the Placed worksheet I would like to include a formula that calculates the fee value cells based on a number of criteria and also using tables. I have uploaded an example spreadsheet.

For example cell N3= if cell A3 = GA and cell L3 >0 <=25000 then L3*.10

The problem I have is that the percentage fee is dependant upon the salary range e.g 0 - 25000 is 10%, 25001 - 50000 is 14% etc, also this changes for each account so for TOB the ranges will be different. I think that I will need to set up tables that the formula then queries, but don't know how to do it. Any help would be greatly appreciated.

[mdmackillop]

I'll await proper sample data and anticipated results.