-
Countifs for Portfolio Summary
Hello! I am trying to do a portfolio summary for my PMO. I want to count the number of active projects in each project stage, for example, active projects in the planning stage. I have a sheet that has a column for if the project is active and another for the project stage, but I don't know how to structure the formula.…
-
I am trying to build a formula to calculate expiry date based on conditionality.
So, if someone fills out the type of PRC as 'VSC', formula should add 'PRC issue date +3 years' to get PRC Expiry date, another condition is, if staff fills out type of PRC as 'CRIC/CIPIC', the formula should add PRC issue date +6 months =IF([PRC Type]@row = "VSC", DATE(YEAR([PRC Issue Date]@row) + 3, MONTH([PRC Issue…
-
Can I use the Substitute function to remove a line-break?
Hello! I'm having success nesting the substitute function to remove multiple things from a field of text in a cell and replace them with a blank space. I would like to remove line-breaks/enter/return and replace them with a semicolon. I'd like to turn something like this: Meredith Rhodes, PhD Meredith Rhodes, PhD Into…
-
VLOOKUP Search value contains multiple Contacts
Hello there, I have a VLOOKUP running right now in which it uses a contact column, references another sheet, and returns the coach of the contact to the next cell. These are entered when coaches meet with their contacts. The trouble is I was just reminded that coaches sometimes meet with multiple contacts in one session.…
-
How can I make a conditional formatting that flags red when two same dates are entered in a column?
I am Trying to configure a vacation calendar for my direct reports, and I want to flag the calendar if more than two associates ask for the same date for vacation. Thanks in advance for the help.
-
Look for a formula within a cell
I'm looking for a way to error check my sheet to find instances of missing formulas. EG someone overwrites the formula(s), and then adds a row, which now is missing the formula(s). Or by adding a blank row (EG Parent) before adding new rows of data. (smartsheet will copy the formulas down to new rows if the previous two…
-
Extracting just the numbers from auto generated Created field
I know how to extract just the time or just the date from auto generated Created and Modified fields. I also know how to remove the : and PM/AM from the time and / from the date. All this done individually. My question - is there a simple formula to extract JUST the numbers from the auto generated field? Or can someone…
-
Exclude Current Row
I am working on a formula where my final piece needs to exclude the row my formula is in. My Current formula is: =MAX(0, (PARENT([FCB LAB]@row) - SUM(CHILDREN([Baseline Labor]@row))) / 45) Can someone show me how to exclude the row my formula is in when summing the children of the Baseline Labor? For further clarification:…
-
Incorrect Argument Set for MATCH, IF, AND formula
Hi there, I feel like I'm close to a solution. I'm working in a symbol column that I would like to automate to turn either Gray or Green. I would like to match a row across different sheets, if specific columns are blank on the other sheet for the match row, I'd like to return a gray symbol, if any one of them is not…
-
PTO Track How to Calculate a specific number (PTO Hrs Earned) on a specific date (Pay day)
Our company calculates the number of PTO hours earned, based on number of years in service and pay period depending on years of service; less than 5 years (7.08), greater than 5 years (8.62). I only want the earned PTO hours added to current PTO hours on Pay Day. Pay Day is every other Friday. I have set up two sheets and…