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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!