-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…
-
Subtracting Months from a date, Error #Invalid Value
I have three columns. One with a date 7/1/2027 in date format. One with a number representing the number of months I wish to subtract from the date column. Third column is a date column with the following equation: =DATE(YEAR([Column4]@row) - INT((MONTH([Column4]@row) - VALUE(Factor1)) / 12), MOD(MONTH([Column4]@row) -…
-
Allow Gantt view to use Date columns with Column Formulas for Start/End
I'm kind of flabbergasted that this is not currently possible in a Sheet, and I'm struggling to explain to my users why. It is already possible to populate a Date column with a Column Formula and then convert it to a Cell Formula, and then use that column as the Start/End for Gantt view / Project Settings, so the issue is…
-
Index, Match formula returning some, not all values
Formula was flowing correctly, but some change seems to have partially broken the pull but I can't figure out what/how. "Project" column dropdown is linked to a Master sheet. Budget Owner column contains the below formula which should look for the cell value in the Project/Cost Center/IO column within the P+CC+IO column of…
-
Duration based on time - formula question
I was able to create a Smartsheet column with time included with the date (Thank you @Paul Newcome & @Akblevins ). I want to add another column that just shows the time - I used the AI Smartsheet feature to create a formula but receive an error message. Any ideas on how to correct? TIA!
-
Help with a formula or formuals
I have 3 columns: Vic By plate number and model, Date Driven(Date Field), and mileage. I am trying to fina a formula or formuals to allow me to see the difference in milage since the last time the plate number has driven. Bonus: In 1 month. Trying to find an easy way to see priority of which vehicle needs to be driven next…
-
Google Map + Longitude & Latitude
I have a column in which I am trying to join a URL and two columns, one for Long, one for Lat. My goal is to write a formula to join them (i've tried, join, i've tried +)…. so that I can click on the link and be taking to the google map for the site. The issue is, the first coordinate is followed by a comma and the second…
-
Tracking a 1-day project based on time not date
I am helping manage a 1-day Disaster Recovery exercise and we want to track the tasks by start & end based on time of day. Would also like to auto adjust the times with dependencies - Does anyone know if a template is available of has anyone ever done this before?
-
Need to update a formula to match items from multiple columns
I have a project currently piloting to update census numbers in animal cages. The formula I have pulls from another sheet and populates the data into the rooms of the same name. The problem I have discovered is there are some spaces that have two cage types we track, but my formula does not account for this. This is what…
-
Having trouble creating a multiplication formula
I used the AI chat to help create a formula to multiply PPU by STOCK and apply the sum to the entire INVENTORY VALUE $ column. =[PPU] * [STOCK] Why is the sum not populating in the "INVENTORY VALUE $" column? I have the properties set to American $… I think. The AI chat said there may be an issue with the sheet set up This…