Forum Discussion
Office Space Ranking System using formulas
I am trying to develop a spreadsheet that will flag users if they warrant a single or shared office space, attached is a rough start I have going.
I am running into 2 main problems:
1. How to get the rank to automatically transfer from the "Ranking" sheet to the "Overview" sheet. For most positions it is a simple 1:1 transfer but looking at D9 you will see a staff member with two titles. I am not sure if there is a way to reconcile the two through formula or if it will have to be done manually. On the ranking sheet I have split them out and given each of them a rank separately.
2. In column H I am assigning points based on years of service table found on the "Ranking" sheet. I think this requires and IF/THEN formula but have not had any success in getting it to work.
3 Replies
Perhaps this one:
=LET( title_pts, XLOOKUP([@Title], Rankings[Title], Rankings[Rank], 0), Service, YEAR(TODAY()) - [@[Year Hired]], service_pts, XLOOKUP(Service, YrsService, points, 0, -1), total, title_pts + service_pts, IF(total > 30, "Yes", "No") )Note: Some of the title names vary slightly between the two sheets. I made them the same so the lookups would work.
Follow up question, I was able to get some of your formulas working. Some I was able to reuse an old one. However in Column L I cannot figure out how to conditional format the "Current Space" to highlight based on the data in Column K.
Basically:
if No = Single then flag
No = Shared
Yes = Single
If Yes = Shared then flagI have tried if/Then formulas but kept getting errors.
Please see the amended
This is the formula for the conditional:
=OR(AND($K2="No",$L2="Single"),AND($K2="Yes",$L2="Shared"))
