-
Formula for finding the column name?
I have a set of data where I am trying to do 2 things: Find the top 5 highest values in a row. I used a LARGE formula to find the values in the 1st-5th highest spots (seen in rows 9-11). Find the name of the columns where the 5 largest values reside, without having to manually look at them. This is the one I can't get to…
-
Count If with Symbols?
I am trying to label the overall health of my projects If all symbols on my range are green, I want the overall cell to have a green symbol. If one or more symbols on my range are yellow, I want the overall cell to have a yellow symbol. If one or more symbols on my range are red, I want the overall cell to have a red…
-
How can I use Index Match Match
I am trying to use index match to pull a contact name from the last person who worked on the project. This one works which is on the same page but I need a version from a different page =INDEX([ASSIGNED ENGINEER]@row, MATCH([JOB NAME]@row, ""), MATCH([JOB NAME]@row, "")) Im following the same rules index the Range of…
-
How to identify the latest record submitted via a form among all entries by the same submitter?
My smartsheet dataset is comprised of form submissions. Businesses are required to submit the form monthly, and I need to assign a "red" color to businesses who have not submitted a form in the last 60 days, and "yellow" to businesses who have not submitted a form in the last 30 days. I do have a separate sheet listing all…
-
Count & Collect giving Error
I have multiple Metrics sheets where I use essentially the same formula to collect information. For some reason I am not seeing, I am getting an error with this one. =COUNT(COLLECT({Row ID}, {Category}, $Label@row, {Wave}, W1B$1)) The intention is to collect the number of Tickets assigned to each category during Hypercare…
-
Dynamic Column Reference
Hi! Lets say I have a basic formula like this in a "budget" column: =SUM([Week 1]@row:[Week 10]@row) And I also have a cell in a helper column that includes this formula: =WEEKNUMBER(TODAY()) Is there a way of [Week 10] changing to [Week X] based on the number in the week number cell? I have a column for every week of the…
-
Formula Copy/Paste Issue
I have the below formula to extra a Quarter based on my column "Expected Close Date". The fiscal year begins 10/1. When I copy the formula to other rows it displays the same number (i.e. January showing as Q4 when formula is pasted when it should be Q2. =IF(MONTH([Expected Close Date]@row) >= 10, "1", IF(MONTH([Expected…
-
Is there a way to simplify formulas with a variable within the formula?
I would like to simplify certain formulas that contain common/repeat functions by assigning a variable. This can be done in MS Excel using the LET function (see image below).
-
Formula to split month and year into two columns
I have data coming into a sheet (Month column) that I need to separate into Month (Helper - Month) and Year (Helper - Year) columns. I have worked out how to get the month separated using the LEFT function, however when trying to use the RIGHT function to get the Year separated, it works where the month is 4 or less…
-
Need help with monthly form tracking formula to Master sheet
Intake Form sheet for each site that has submitted their monthly activity form. Entries will be in this sheet for the entire current year, so each site ‘might’ have up to 12 monthly entries. Each of the 60 sites can have multiple entries, the 2 sheets are linked with Site ID (form sheet) Site # Text (master sheet) If the…
