automatically fill rate column based on another table with resource rates

jodo2

New Member
Joined
Feb 25, 2018
Messages
3
Hello everyone,

I've researched this a lot but I couldn't figure out how to accomplish it, so I'm turning for some help. Would appreciate any guidance in advance. The difficult part for me is that the same person could have a different role on different project code/streams. I'm doing it manually right now and sometimes I fill in the wrong rate. I'd love to automate that part of it.

So I have one tab where all the data is, where I fill in the weekly timesheets of resources. The columns are:
Date, Project Code, Stream, Name, Description, Rate, Hours Worked

So I wanted a way to automatically prefill the Rate column based on the data I have in another tab. That tab called AssignmentTracking and the columns are as follows:

Name, Role, Project Code, Stream, Assignment Start Date
1. John Smith, Developer, 123456, Packaging, Feb 01, 2018
2. John Smith, Business Analyst, 101010, Shipping, Feb 01, 2018

People send me their timesheets weekly, and I input the information into the first tracking tab. All is good except the rate bit I have trouble with, for the most part I have it memorized but I'd love to do something to automate it.

Thanks in advance for any help.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
anyone have an idea on how to do this? I've tried vlookup and index , match, etc. but cannot figure it out.
 
Upvote 0
It doesn't look like you have the rate data anywhere other than your manual entry tab. There doesn't appear to be a column for rate in your AssignmentTracking.
You need to have a tab that shows the rate in column B and the criteria for that rate in column A.

I'm looking to do something similar but i need the rate to change based on increased wages and the date of that increase.
 
Upvote 0
It doesn't look like you have the rate data anywhere other than your manual entry tab. There doesn't appear to be a column for rate in your AssignmentTracking.
You need to have a tab that shows the rate in column B and the criteria for that rate in column A.

I'm looking to do something similar but i need the rate to change based on increased wages and the date of that increase.

Oh yeah, you are right, I forgot to include that , it's too bad I can't post the excel file on this forum

I did intend to have a column for rate
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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