How can i create this formula

I need an excel file with a sheet (Pay Table) that has a list of employees in a column and their corresponding pay rate in the next column.   This list needs to be expandable (1 - 200 employees).  

I also need another sheet (Calculations) that has an employee column,  an hours column and a total dollars column.   This sheet needs to be unlimited (or whatever the excel limit might be) in rows.


What i need is a formula that gets the employee name entered on the Calculations sheet,  then grab the corresponding pay rate from the Pay Table sheet, then multiply that pay rate by the hours from the Calculations sheet and enter that in the corresponding Total Dollars column.


I attached a workbook to help out with my explanation.


Thanks for any assistance.
Book1.xlsx
LVL 23
tailoreddigitalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached and see if this is what you were trying to achieve.
PayTable.xlsx
tailoreddigitalAuthor Commented:
Yes, i think that's perfect.    Where is the formula placed?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The formula is on Calculation Sheet in Col. G.
I have copied the formula down to G1000 so for 1000 rows you don't need to do anything, you just need to input Employees in col. C and Hours in col. E and corresponding cell in col. G will be populated automatically if the Employee is present on Pay Table Sheet and Hours cell is not empty.

Moreover if an employee is not present on Pay Table Sheet, the formula will return Employee Not Found in col. G.

If the need arises, you may copy the formula in col. G down the rows as far as required.
To do that, just select any formula cell in col. G and grab the fill handle (bottom right corner of the cell) and drag it down the rows.

Hope this information helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

tailoreddigitalAuthor Commented:
You've been very helpful and accurate with what i need to accomplish.

I was just enlightened to an expansion of this file.    Maybe i should be asking in another question?   You have answered this question perfectly.  

I think i can figure this, but i'll throw the adjustment at you for your idea and expertise.

I need multiple Employee and Hours columns to contribute to the same Total Dollars.

How can i adjust this formula so that,

         B               C               D                E                F                G                H                I                  J                K                L                M                N
Employee | Hours | Employee | Hours | Employee | Hours | Employee | Hours | Employee | Hours | Employee | Hours | Total Dollars

So i need to calculate each employee hours (and some may be blank) then put that total in N.

          B               C               D                E                F                G                H                I                  J                K                L                M                N
chad           |    1.5    |    Dan      |     4.2    |    Eric       |     5.2    | Employee | Hours | Employee | Hours | Employee | Hours |    Total Dollars

i.e.     N (Total Dollars) = chad pay rate x 1.5  +  Dan pay rate x 4.2  +  Eric pay rate x 5.2
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please open another question as your original question has been answered. :)
tailoreddigitalAuthor Commented:
Exactly what i was looking for. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.