Complex VLOOK and/or Index formula question. Expert advice needed

Hi - I am in need of help. I am trying to create a workbook that will allow me to report the hours, cost, and revenue for a budget that I manage. Instead of manually typing this information in for 100 + employees each month, I would like to have a formula that will allow me to download the current month data right into excel and then have the formula populate all the data for me.

I have attached the workbook that I am trying to create and posted some information below on what is specific to my needs. Can someone please help me in creating a VLOOK and/or an Index formula that will allow this file to work? I am looking for the easiest way possible to do this, so if there is another way to create this formula, I am open to that as well.

- I will have multiple budgets that I need to manage, they will be separated by the Job No that is on each row of the Hours, Cost, and Revenue tables within the Table tab
- Within the Job No there will be multiple employees that I need to pull in information from the Data tab. I am only using one for now to make this formula as simple as possible.
- Each employees unique characteristic is their employee ID No. I would like to have the formula written with that being the main lookup.
- When I update the actuals each month, it is very common for the employee to have multiple rows of data for the current month. I need the formula to be able to look up all of the rows of data within that current month and update the actuals based on the total for that month.
-I will have three different sets of data that I need to pull for each employee, they are Hours, Cost, & Revenue
-I can not manipulate the Data tab in anyway. The way that the numbers are showing currently in the workbook are how they will appear in the download from the database.

If anyone can please help me get this started, I would be very appreciative!!

Attachment 216816

Thank you very much for your response. The formula you created works. I expect to have 12-18 months worth of data in here, will the formula need to have additional criteria added to it for every new month that is added in? Would there be another way to create this formula if we had a few other tabs created that were referenced when looking up data? Just trying to think of this in the most straight forward way.

v/r,
GRDecker

Hi - I added the month of March into the Tables and copied the formula over for each of the Hours, Cost, and Revenue tables. I copied from Feb to March. I then added charges for the month of March in the data tab. How come the data for March does not appear in the Tables now? Is there something else that needed to be added to the formula? Updated spreadsheet attached.

Excel Forum Sample 1v2.xlsx

Thanks

Excel Forum Sample 1v3.xlsx

I created a formula based on what I needed to do. Do you think you could take a look at how this works and see if there are any flaws? The one major problem I have right now is that the formula is updating the total hours for that one person as oppose to just the specific month.

Originally Posted by p24leclerc

try those changes:
in your Budget sheet, the formulas could be:

Please Login or Register  to view this content.

While in your PCR sheet, the formula in column H should be change to:

Please Login or Register  to view this content.

Awesome! This worked out! Thank you for all your help!

OK - so now I am having an issue where your formula is working, but in some instances it is still adding up the totals for all the months in multiple months. Can you take a look at the highlighted row and tell me what I am doing wrong??

Excel Forum Sample 1v4.xlsx

Originally Posted by SDCh

try this on sheet Budget column F7, for copy drag across and down

Please Login or Register  to view this content.

Note: use ctrl+shift+enter, if just enter the result will be wrong.

Excellent. This worked!! So what do those brackets do exactly? I know it has to do with an array formula...