0

SUMMARY

I have a cell (K16) that references to another sheet for when someone has been promoted, and another cell (M16) that works out if the person is still on their 1 day cooldown by checking it against the date they were promoted. (K16). I want to be able to make it so when I start to promote someone, it highlights if they are still on cooldown, however, I cannot work out how to do this because cell K16 is equal to =IFS(ISBLANK(E16),"",MAX(INDEX((E16='Promo/Demo Log'!D$5:D)*'Promo/Demo Log'!C$5:C))=0,(MAX(INDEX((G16='Tryout Log'!D$5:D)*'Tryout Log'!B$5:B))),ISNUMBER(E16),MAX(INDEX((E16='Promo/Demo Log'!D$5:D)*'Promo/Demo Log'!C$5:C)))

For reference E16 is a person's ID so it searches for their ID which is what's used to fill in a person's promotion.

This is causing me issues as K16 instantly updates to the new date as soon as I fill it in on the promotion log, which means that the check I currently have on the Promo/Demo Log which is meant to see if the person is on cooldown still is useless as when it checks, K16 has already been updated to the new date so it is constantly telling me the person is on cooldown.

The code I have that checks this is: =IFERROR(IF(VLOOKUP(D14,{Roster!$E:$E,Roster!$D:$D},2,FALSE) =H14,INDEX(Roster!M:M,MATCH(TRUE,ISNUMBER(SEARCH(D14,Roster!E:E)),0) ),0)) D14 is the person's ID on the 'Promo/Demo Log' so it searches the roster for that same ID. Column E on the 'Roster' sheet is where the person's ID would be on the roster and column D on the 'Roster' sheet is their rank which checks against H14 on 'Promo/Demo Log' and if the rank is equal then it displays the date. This works but isn't fully needed, I only added the check rank bit to try to fix the issue I was having.

FULL DETAIL

So, I am currently making this for an online game I'm playing, and for some context the roster is used for a regiment within the game to list the members in that regiment. The different sheets on it are as follows:

Roster: - Main sheet that displays everyone currently in the regiment Tryout Log: - The page where people will fill in who they did a tryout for as well as their own information. Member Data: - When someone joins the regiment, their information is put on this sheet and they are assigned a unique number. Promo/Demo Log: - The log where peoples promotions and demotions are filled out and by who.

On the 'Roster' sheet the main bit of code that is referenced is in Column K, titled 'Last Promotion' and column M, titled 'Cooldown Expired'.

I will attempt to describe my issue as best as I can.

Whenever anyone joins the regiment, this is started by a tryout, the first thing after a tryout is to fill in the 'Tryout Log' sheet with the person's tryout information (their name, SteamID, Discord and Region). The person who hosted the tryout also has to fill in their information and date when the tryout took place. Once the information is filled in, they then go to the 'Member Data' sheet and fill in the new person's information (whoever just passed the tryout) to assign them a unique ID (a number). The reason for this is some code on the roster like the persons name, steamID and discord use the unique ID to lookup the information.

After someone has been given that Unique ID on the Member Data sheet, on the 'Roster' sheet you then fill in the ID box with the person's assigned unique number, that then auto generates the rest of their information. One of those cells is the 'Last Promotion' cell (in Column K) which searches for the person's ID in the 'Promo/Demo Log', if it can't find it in there (because the person is a Private and just joined the regiment through a tryout so hasn't been promoted yet) it then searches the 'Tryout Log' sheet and returns the very last date that matches up to that person's ID, so you can see when they most recently joined the regiment or were promoted.

The next column over from that is the 'Cooldown Expired' column (Column M). This just calculates if they are off cooldown using: =IF(AND(K16<=TODAY()-1,A16=1), TRUE, FALSE) --K16 is the cell in which the date the person was last promoted is in.

Now, my main issue relies on the 'Promo/Demo Log' page. I am attempting to make it so as someone fills out a promotion, when they put in the Date first and then the person who has been promoted's unique ID, if that person is STILL on cooldown it'll be highlighted so that people aren't accidentally promoted while still on cooldown. However, due to the nature of the people in the regiment moving up and down in ranks and their date of last promotion changing all the time, I have found this very difficult to do.

When I first thought I'd managed to do it, I found that when you enter in the date on the 'Promo/Demo Log' to log the persons promotion, as soon as you put the unique ID next to it, because column K on the 'Roster' sheet is actively looking for the persons ID and a date of promotion, the roster automatically updates itself to say that the person was promoted to whatever the date that was just filled in was. E.g if someone was last promoted on the 7th Feb 2022 and they have a 1 day cooldown, if on the 9th Feb 2022 the person was then promoted and whoever was filling in the promotion started filling in the log, as soon as they put in 09/02/2022 and the person's ID, the roster will instantly change to 09/02/2022 from 07/02/2022. This usually would be good but because I wanted a check to be carried out on the 'Promo/Demo Log' to check if the person really was off cooldown, this breaks that as they instantly are not off cooldown because the date has just changed to the day they were promoted, which ruins the whole point of the check as it will always say the person being promoted is still on cooldown.

All code I've used and attempted to solve this problem with are on the sheet, I've tried to annotate it as best as I could to help anyone understand the issue I'm having.

I apologise for such a long description, especially as I know it's going to be very confusing. I've spent days trying to solve this, so hopefully this will be challenging for someone else and you won't take 5 minutes to solve it...

Any help is appreciated, thanks :) If anyone here does use Discord and would like to get in contact with me via that, my Discord is louis#8564

Below is a link to the Google Sheet: https://docs.google.com/spreadsheets/d/1q0dL1eQE9HL0kQTTTjPcicooqtvCBc-5mWb1PWtYz2s/edit?usp=sharing

Improve this question
5
  • HI and welcome to WebApps. Q1: in the event that a cooldown period has not expired, is your goal to render a "warning" (which a user might choose to ignore), or to "stop" the promotion from proceeding (i.e. no user discretion)? Q2: is there a finite list of promotions, and can a player 'jump' a promotion?
    – Tedinoz
    Feb 12, 2022 at 4:31
  • FWIW, I think the cooldown date should be 'calculated' on "Promo/Demo Log" and merely displayed on "Roster". It is more important to focus on the process of creating a promotion, and ALL the steps involved and these should be on "Promo/Demo Log".
    – Tedinoz
    Feb 12, 2022 at 4:34
  • @Tedinoz Hi, for Q1 if their cooldown period has not expired my aim is just to create a warning which they can ignore, as in some circumstances people can skip cooldowns. For Q2, when you say finite list of promotions, a person can essentially be promoted from 'Private' and up to each individual rank until 'Colonel'. If that's not what you meant, then I can only assume you meant on 'Promo/Demo Log', that log will just continue forever of different people being promoted. Players can jump promotions as well yes, doesn't happen often but people can skip certain ranks.
    – Fresh Tingz
    Feb 12, 2022 at 15:33
  • @Tedinoz The cooldown date is calculated on the 'Roster' sheet just so that someone could quickly glance at the 'Roster' to know if they were off cooldown first, rather than having to start filling out a promotion on the 'Promo/Demo Log' before knowing it. I just wanted to create a warning on the Promo/Demo Log in case the person hasn't bothered to check the 'Roster' sheet to see if they were off cooldown yet, so a sort of backup to stop people being promoted off cooldown when they shouldn't be.
    – Fresh Tingz
    Feb 12, 2022 at 15:37
  • Yep, I get it. And I don’t mean that you should take the cooldown display off the “Roster” sheet. But, personally, I would choose one sheet for everything to do with promotions (except maybe a “definitive list of promotions”). An option for the roster sheet might be to sort by “ID”, by “promotion date”. This would highlight the “current” status (promotion & cooldown-wise) for each player. An option option would be to use a script (which is starting to complicate thing slightly) and a “proposed promotion” range; it would DEFINITIVELY stop a user from being promoted during cooldown.
    – Tedinoz
    Feb 13, 2022 at 0:19

1 Answer 1

Reset to default
0

My own view is that the "Roster" sheet should display the current Cooldown date, but that the "Promo/Demo Logs" should contain all the elements required to establish whether a player is entitled to be promoted and to which grade.

This answer creates a "Status display" (for the want of a better term) for any proposed user promotion. The display will indicate whether the promotion can proceed or not. The images below show a selection of the "user name", but this can be easily changed to select the User ID.

Though the answer doesn't include this, by adding a checkbox and an onEdit(e) script, a valid promotion could be easily/simply updated to the "Promo/Demo Logs" sheet.

Formula and comments

  • Sample data includes a recent promotion for "Christian"
  • Existing Column C isn't used
  • Column D = Promotion Date (manually entered, or could be created by script)
  • Column E = Cooldown Date = Promotion Date +1
    • e.g. Cell E11 = =D11+1
  • Conditional formatting in Column E
    cooldownrules
  • Range D4:F9 is used to establish whether a player can be promoted
  • Cell E4: Name - Data Validation based on "Ranks Table"
  • Cell E5: ID - =vlookup(E4,{'Member Data'!D4:D13,'Member Data'!C4:C13},2,0)
  • Cell E6: Latest Cooldown Date - =query({$D$11:$J},"select max(Col2) where Col1 is not Null and Col3 matches'"&E5&"' group by Col3 label max(Col2) ''")
  • Cell E7: Status - =if(query({D11:F},"select max(Col2) where Col1 is not Null and Col3 matches'"&E5&"' group by Col3 label max(Col2) ''")>=today(),"Not permitted", "OK to proceed")
  • Cell E8: Current Rank - =query({$D$11:$J},"select Col7 where Col1 is not Null and Col3 matches'"&E5&"' and Col2=date '"&TEXT(E6,"yyyy-mm-dd")&"'")
  • Cell F8: Current insignia - =image(vlookup(E8,ranks!$A$3:$D$22,2,0))
  • Cell E9: Next Rank - =if(E7="Not permitted",na(),vlookup(E8,ranks!$A$3:$D$22,3,0))
  • Cell F9: Next insignia - =image(vlookup(E9,ranks!$A$3:$D$22,2,0))
  • Cells E6 and E7 have Conditional Formatting Rules
    current rules

Sample - Cooldown fail

Fail

Sample - Cooldown pass

pass

Ranks table

ranks

Improve this answer
2
  • Is there any way I can get a link to this to make a copy to look at it in full to make it easier to understand?
    – Fresh Tingz
    Feb 13, 2022 at 18:23
  • Refer your gmail
    – Tedinoz
    Feb 15, 2022 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.