Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Combo a vlookup/index formula to pull a value from one sheet with cond formatting to another sheet?

Scenario: I have a Project A Plan sheet that has a field for Bill Rate and Role. This bill rate value is pulled in from a Client A Rate sheet via vlookup based on the value in the Role column on the project plan sheet. This formula is currently working fine.

The Project A Plan sheet also has a column for employee name (which isn't used in the above formula but is important to the use case).

I also set up 3rd sheet we'll call Min Rate sheet that has 2 columns- Employee Name and Minimum Rate for each employee.

For this use case, I wanted to see if I could add a conditional formatting to the Bill Rate cell (on the Project Plan sheet) that compares the Bill Rate value to the Minimum Rate cell for an employee on the Min Rate sheet that makes the Bill Rate cell red if the Bill Rate is less than the minimum Rate.

Is this or something similar possible in SS?

Best Answer

  • Community Champion
    Answer ✓

    Hey @ParkerLeigh

    Yes, it is possible however it will need a helper column on your Project Plan sheet to accomplish. A checkbox column will work.

    The helper column formula will do the actual comparison between your current rate and min rate. Set your conditional formatting to highlight if the helper column cell is checked.

    =IF([Bill Rate]@row < INDEX({Min Rate sheet Minimum Rate column},MATCH([Employee Name]@row,{Min Rate sheet Employee Name column},0)),1)

    Remember since the formula contains cross-sheet references you will need to manually create the references - you cannot simply copy paste the formula.

    Will this work for you? Shout out if you have any trouble

    Kelly

Answers

  • Community Champion
    Answer ✓

    Hey @ParkerLeigh

    Yes, it is possible however it will need a helper column on your Project Plan sheet to accomplish. A checkbox column will work.

    The helper column formula will do the actual comparison between your current rate and min rate. Set your conditional formatting to highlight if the helper column cell is checked.

    =IF([Bill Rate]@row < INDEX({Min Rate sheet Minimum Rate column},MATCH([Employee Name]@row,{Min Rate sheet Employee Name column},0)),1)

    Remember since the formula contains cross-sheet references you will need to manually create the references - you cannot simply copy paste the formula.

    Will this work for you? Shout out if you have any trouble

    Kelly

  • @Kelly Moore thanks for the reply! that makes sense and if i can hide the helper column and it still applies the conditional formatting to the bill rate cell then i think we're cooking!

    i'll def reach back out if i run into any issues- thanks again!

  • @Kelly Moore i had to mess with the formula some, but got it to work! Thanks again for the assist!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions