Simple Question

Jeffrey Brooks

I want to use a sheet titled ECLT with certain values in it for wages - i.e. $25.00 per hour for John Doe. That value is in the column titled "Rate." John's rate is on line 13.

In my primary sheet, there is a column which includes hours worked by John Doe - those hours are on line 7. I want to multiply those hours times the rate on line 10 in the second sheet to total the gross amount owed to John Doe.

I would have thought I could have used this formula:

=({ECLT [RATE]13}) * ([JD HRS]7)

Did not work. I would appreciate any and all help!

Thank you

Jeff

Maddie Simonsen

Hi Jeffrey,

For a cell or range of cells to be referenced from another sheet, you can use an INDEX(MATCH) function.

On your primary sheet you will need the unique identifier, in this case, "John Doe", in a cell that can be referenced to identify who's rate you need to make the calculated rate (hrs*rate) and this unique identifier should also be a cell in the same row you have the worker's rate listed on your 2nd sheet.

here's the formula:

=[hours worked]@row * (INDEX({rate range}, MATCH([Worker's Name]@row, {Name Range}, 0)))

formula breakdown

[hours worked]@row is the hours you have on line 7 on primary sheet

**note @row is best practice to use rather than the line # next to the column name (instead of [JD HRS]7, [JD HRS]@row)

{rate range} are the cells you select in the column on the 2nd sheet that have the worker's hourly rate

[Worker's Name]@row is the cell on your primary sheet that you are looking to reference on the 2nd sheet.

{Name Range} is the range of worker's names on 2nd sheet that will be used to cross reference to find the correct rate/hr

When building the formula on your sheet, you will click on the Reference Another Sheet link that pops up so you are able to select the above mentioned ranges on your 2nd sheet.

pics for reference...

formula on primary sheet:

formula result on primary sheet:

rates on 2nd sheet with name as unique identifier:

Here's additional info on cross-sheet references: https://www.smartsheet.com/content-center/best-practices/tips-tricks/powerful-and-flexible-cross-sheet-formulas

Jeffrey Brooks

I would like to say thank you for the time and the effort you put into this reply - it may take me a few minutes to work through it but you really went above and beyond. Thank you!

Jeff

Maddie Simonsen

Happy to help!