Forum Discussion

kerry590's avatar
kerry590
Copper Contributor
Jul 26, 2023

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 ge...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Jul 26, 2023

kerry590 

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.

kerry590's avatar
kerry590
Copper Contributor
Jul 31, 2023

Patrick2788 

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 flag

 

I have tried if/Then formulas but kept getting errors.

 

Please see the amended 

Resources