-
Want to verify cells are equal, but getting reversed 'true' 'false' values in IF() statement...
I have a large SmartSheet that contains deposit information for my company plus others we do billing for. Since payments come in long after the service date in healthcare, I may get an outlier payment for a company that is no longer active in current year, but need to process. I've created a cell 'All Deposits' that is the…
-
using index/collect to gather data from 2 sheets
Hello - I have several budget sheets that use an "index/collect" formula to grab information from a master data sheet. However, we are now moving some rows from the master data sheet to an archived master data sheet. Once that row is moved, all of the index/collect cells are broken since the original row with the data is…
-
Date Modified
I have a sheet with a list of assets. There is a pull down menu that defines the status - I need to know when the menu is set to "complete". I can do that easily with a filter - but I want to cull the entries before a specific date. I have a Report that uses the 'is greater than-date modified' but I get results from all…
-
Nested formula to present the most recent KPI value from a row
I am trying to pull the most recent month's value to the 'Current Month Data' cell to use for Dashboard Widgets that will only present the Goal and Current Month Data. There are 30+ KPI values that need to have the latest month's value, and I'm stumped on how to build the nested formula to make it work. The same formula…
-
IF/AND for date range and TODAY help
I'm looking to return phase names based on if today's date falls within specific time ranges on another smartsheet. For example: I have start and end columns/dates on one sheet for "Phase 1," "Phase 2," and "Phase 3" for different projects. On another sheet, I'd like to return "Phase 1," "Phase 2," or "Phase 3" if TODAY…
-
Summing a Multi-Select
I'm trying to figure out an easy/dynamic way to sum a column. Right now, I have Column A in Sheet 1 that is a multi-select. I want to lookup the result of that multi-select in Sheet 2, and total the values from Column B. So I know how to do it manually (i.e. - by spelling out all the values using FIND () or HAS ()) as part…
-
average progress depends on criteria and vlookup
Hello all, we are trying the following: In a new sheet we want to reproduce some information from the source file using the VLOOKUP function. So far so good. Now the question is, if there is a formula, which from the Children lines outputs the average progress, where the lead is called "Kunde". There can be several lines…
-
How to select value from last column in a range
Hi! Would love to get feedback on how to create a "Current Status" formula that looks by row across columns to pull the latest value. What I want is a formula that updates automatically as I populate columns with weekly Account Status. Below is a screenshot that shows the columns I am working with. The leftmost column is…
-
Automation of Status Drop Down at the Parent Level
Hi, I want to automate the status column of the parent row based on the child rows. For example, if all of the child rows are left blank or marked "Not Started," I want the parent row to read, "Not Started" Or if a majority of the child rows are marked "In Progress" and some are marked "Complete" I'd like the parent row to…
-
Formula for Health based on Week
Hi everyone - I have a Project Plan with an associated Master Tracker & Metrics Sheet. I am wanting to pull in the Health (RYG symbol column) based on the week. An example of my Project Plan is below: You'll notice I have a "Week (Helper Column)" which helps me with calculating which week a project is at any given time.…
