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
Microsoft Excel

Last Comment
tailoreddigital
Subodh Tiwari (Neeraj)

Please find the attached and see if this is what you were trying to achieve.
PayTable.xlsx

Yes, i think that's perfect.    Where is the formula placed?
Subodh Tiwari (Neeraj)

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

Exactly what i was looking for. Thanks
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY