I need help creating a formula that will automatically fill column C with priority text like “P1”, “P2”, or “P3” based on what value appears in column H.
Here’s what I’m trying to do: When cell H1 contains something like “XB02C15”, I want the system to check which priority category this code belongs to and then put the matching priority in cell C1.
The tricky part is that I need this to work even if someone manually changed the priority before. So if C1 was set to a lower priority manually, but the lookup shows it should be higher priority, I want it to update to the higher one.
I also want to keep the dropdown validation in column C so users can still change priorities by hand if needed. The formula shouldn’t break this functionality.
Is there a way to set this up so it automatically updates priorities but still allows manual overrides through the dropdown menu?
yeah totally get it! i had the same issue, and using helper columns is a great way to keep things clear. just remember that you gotta keep an eye on the dropdown options in case of any conflicts. messy or not, it keeps the flexibility!
To achieve the desired functionality, utilize ARRAYFORMULA combined with IF and VLOOKUP. Start by establishing a separate sheet that houses your code-to-priority mappings. You can test with the formula: =IF(C1<>"",C1,VLOOKUP(H1,LookupSheet!A:B,2,FALSE)). However, it’s important to note that Google Sheets does not allow both formula and manual input in the same cell, as manual entry will overwrite the existing formula. A possible workaround I encountered was implementing a script that activates on edits to check if the manually set priority is lower than the lookup value and updates it accordingly. Ensure your dropdown validation remains intact; it should function with formulas provided you set up the data validation before applying the formula.
Google Apps Script worked great for this. I set it up to trigger whenever column H changes, then it checks if the lookup priority is higher than what’s already in column C. Only updates if the new priority beats the current one.
Best part? Your dropdown validation keeps working perfectly since the script acts like manual entry instead of formulas. I threw the code mappings on a hidden sheet and used getRange().getValues() for lookups. Takes maybe 10 minutes to set up initially, but you’ll save yourself tons of headaches. The trigger runs everything automatically while you keep full control over manual edits.