-
How to build a row amortization formula
I have the following fields in smartsheet: Value: 10,000 Remaining Periods: 20 Amortization per Period: 500 I am able to create a row amortization in excel with a formula based on the "Column name", but unsure how to do this in smartsheet since I can't reference column name. Is there a way I can easily recreate this? (see…
-
Cross reference another sheet and filter the criteria
Looking for a formula to pull data from another sheet and add 2 filter criteria. Here is my filter critera but not sure how to reference the source smartsheet. =COUNTIFS([Client Mitigation Status]:[Client Mitigation Status], "Pending") + COUNTIFS(SBG:SBG, "AERO")
-
Clarification regarding Average(collect)?
I'm trying to calculate the average lifespan for a specific machine in a specific area, but am running into an issue with my average(collect) formula. Here's the formula I have right now, and it only returns as unparseable. =AVERAGE(COLLECT({Lifespan},{Machine},"1",{Row},"1",{Position},"1")) In this instance, its to…
-
Workflow for After Hours Tickets
Hi all, I currently manage our support tickets smartsheet and live on east coast. I have a team member who lives on West Coast and I want to create a workflow to have any tickets submitted between the hours of 5pm EST and 8PM EST sent to him with a notification. Currently, I am capturing the date/time ticket is submitted…
-
I think I need an index match formula as source sheet is unsorted
Hi, people smarter than me. I have been trying for days and days to get a formula to work. The source data is not sorted; it is populated by form data. The vloopup formula only duplicated the same result from the first row over again. =IF(VLOOKUP([Primary Column]@row, {2024 Circulation File Num to Support Level}, 1, false)…
-
Percentages lost when indenting linked cells aka best way for roll up smartsheet
Hello I am trying to connect about 50 project plan sheets on one sheet. The requested configuration is a traffic indicator for a customer then the ability to drill down to see each customer project and status then a drill down for high-level milestones. FYI I have just read about the limitations so I understand eventually…
-
Incorrect Argument Set error when using Vlookup to reference another sheet.
I am using a Vlookup to use a row id in sheet A to lookup the corresponding row id in sheet B and return the value in the status column - the syntax looks correct to me but I am getting an Incorrect Argument Set error. This is the syntax I am using: =VLOOKUP([Row ID]@row, {Cutover Tasks Range - Row ID}, 6, false) Can you…
-
How to calculate using a 7 day workweek (to replace NETWORKDAY)?
I am using this formula to calculate the # of days it takes to get to a particular task called, Enable. Range 2 = %Complete and Range 1 = Start Date of Enable. It calculates correctly using 5 days per week. I am now being asked to count using 7 days per week. Is there a comparable function that I can swap out for…
-
Creating a unique ID based on two other columns
I'm trying to create a unique id based on values in 2 columns: partner and label. In our data, we have names associated with partners, but when we share data we don't want to share names, so instead I'd like to create IDs. As you can see in the sample below, we use two columns because names can be repeated between…
-
SUM formula with extending range
Is it possibly to have a SUM formula in a cell at the top of a column, calculating the sum of the contents of that said column, with the range beginning at a specific row but continuing to the bottom of the sheet and extending down as rows are added to the sheet automatically? It would obviously be easier for me to just…