Set a field value dependant on multiple criteria

Rafiki_Runner

New Member
Joined
Apr 25, 2018
Messages
3
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top