-
Setting up VLOOKUP correctly
Hello, I have a source sheet that has a unique ID, and I use that as a reference to generate a number of references in a second sheet for extra details. I am using VLOOKUP to bring about 10 columns from the source sheet into the second sheet. The typical formula looks like this: =VLOOKUP([AOP ID]@row, {2023 AOP Entry Range…
-
Overlapping Dates and Auto-Flagging
Hello, I have created a communications plan for my organization. I would am trying to auto-flag (in the Overlap row) any communication entry that has overlapping Start/End Dates of the same Delivery Method. I am using the formula below, but receive the #unparseable error. What am I doing wrong? =IF(COUNTIFS(Delivery…
-
Turn Rolling 12 Month report into Rolling 24 Month Report
Using help from a discussion on Smartsheet Community, I have managed to create a rolling 12 month report, using the following formulas Month: =IF(MONTH(TODAY()) - [M1]$1 < 1, MONTH(TODAY()) + (12 - [M1]$1), MONTH(TODAY()) - [M1]$1) Year: =IF(MONTH(TODAY()) - [M1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY())) However, if I…
-
Time Tracking Formula
I need to track the hours of our paid on call fireman. I wanted to be able to create a form that asks the Incident number, date, time in, time out, and total time. For each incident, I selected the members that were on the call. On another sheet? I have each member listed out and a breakdown of the total hours for each…
-
Index/Match/Contains formula not working in all cases
I have a question regarding an Index/Match formula. This is for a cross-sheet function. I have two sheets: Call them "Project" and "Jeopardy" Project sheet columns: PID: this is a 6 or 7 digit number identifying the Project Path to Green: this is the "Path to Green" for a Jeopardy against the project. This is where the…
-
How to Sum up Children Rows when another Column is Checked?
Hello all! I have been searching and trying for an answer most of the afternoon now, with no success. I have a budget sheet I am working on that needs to sum up the children Amount rows in a month when a checkbox in the Reflected in Account checkbox is checked. So far I have been trying this formula:…
-
Lookup value in next blank row (W/Reference Another Sheet)
We are trying to auto-number rows once we have an approval completed. We cannot use the auto-number function, as the owner would like to keep the numbering order as the order they were approved. The sheet is grouped into different categories, making it so the lines are not in order of when they will be approved. I created…
-
IF string results in blank than give back text
Hello, I have the below formula that looks at two different sheets to determine the status of a test result. The first index collect logic is looking for the Lot ID AND the test type are both found on the same row of the Test Log sheet. If he has found both then it looks up what the test status is and returns it back to…
-
Data Shuttle and Join Collect formulas
Hello, I have a formula JOIN/COLLECT formula that works perfectly fine when referencing a vlookup sheet with manually entered data. Once that sheet is replaced with data shuttle workflow, the JOIN/COLLECT formula no longer displays data, resulting in an empty return (no error). All vlookup references properly reference the…
-
Highlight a specific cell not the entire column/row
I'm looking to only have child rows highlighted when the value is greater than one. Currently conditional formatting only allows for applying a format to an entire row, or to all the cells within a column (Parent + Child) are there any formulas I can embed in a child row cell that will only highlight for that specific cell?
