Set a field value dependant on multiple criteria


New Member
Apr 25, 2018
I am hoping somebody can suggest a simplified version of what I currently have.

I am tracking time spent on a project by other team members and have an extremely over complicated way of working out the costs.
I created a user form for inputting the hours to make it simpler for the team and avoid them being overwhelmed, however the data from that submits to a hidden sheet that contains columns right up to HB (a lot of columns!) This calculates all the costs, which are somewhat more concisely reported in a different tab.
I don't like it, but hey, if it aint broke, don't fix it.
but then things changed slightly...!
I have three different tasks, three different locations and four different grades of staff. I therefore have to calculate the costs depending on what was done, who did it and where they are based. i.e Consulting by a Senior Project Manager in the UK. or QC checking by a Junior Associate in India

Below is an example of the nested IF statements I have used. This calculates the cost (the metadata tab has the predefined hourly rates in a table and the L5 cell quoted is the number of hours submitted in the userform)
=IF($C5="US-Grade 2",$L5*Metadata!$B$12,(IF($C5="US-Grade 3",$L5*Metadata!$C$12,(IF('Timesheet (T&M)'!$C5="US-Grade 4",'Timesheet (T&M)'!$L5*Metadata!$D$12)))))

This calculates the cost for one of the tasks, for one location. I therefore have this repeated for the other two tasks, then all of that again for the other two locations. (then a whole load of columns to break those hours out dependant on the month the hours were worked, for reporting purposes - but that's a whole different headache!!)

As I say, it's untidy and most likely overly complicated, but it worked.
Now though, there have been some changes to the structures and we will have 6 grades of staff with 2 additional locations and perhaps further tasks with these broken down to a more granular level.

Is there any simple way of looking at three different fields 'Grade' 'Location' 'Task' and pulling the cost that should be associated with the three when combined?

Thanks all

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Well-known Member
Oct 13, 2007
Your specific issue would require more detail and perhaps better, the workbook itself. But for future, perhaps create 2 workbooks.
1) Containing userform (all users can even have their own copy since it will not contain the data/hidden sheet)
2) Containing data

Every time the user attempts to open the userform, a macro would check if the data workbook is already open and instruct the user to try again in moment (reason people use Access instead of Excel)
This method would allow you to have a "configurations" sheet on the data workbook which you could easily adjust to allow for new grades of staff and locations.
Your formula would then rely on a lookup table of staff grades and locations instead of being hardcoded into the formula. I'd suppose something like this:

=if(index(Metadata!B:B, match(c5, gradetable,0))...

That way you wouldn't have to keep storing the staff grades within the formula line.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics