-
Analyzing Text for Special Characters in a Conditional Formula
I have two sheets. Sheet 1 (Character Table) has a list of non-alphanumeric text symbols (e.g., !, @, #, $, etc.) and alphanumeric characters (A-Z, a-z and 0-9) alongside their ASCII values. This table distinguishes the non-alphanumeric values from the alphanumeric ones by classifying their types as "Special" or…
-
Dashboard Chart Data Selection
When I add a chart to a dashboard, it prompts me to select a sheet, but not the columns to display in the chart. If I try to select data fields of categorical labels and data that are not next to each other, I get an error. How do I select the data to display and can it be summarized? For example, I have a sheet tracking…
-
What is wrong with my automated colour column formula?
I am looking to automate the colour “status” for my rows based on the health of the action - a calculation based on the ‘% Complete’ and the ‘Due Date’. The goal is to demonstrate a contextual health status. For example, even if an action is only 10%, if the due date is still 6+ months away, the action is still Green due…
-
Help with VLOOKUP
I am trying to pull data from one sheet into a summary sheet. Basically I am looking to pull if a given site met a target on a given day -- an admin is entering in the number of employees reporting for work on a day for each of 12 sites into a Smartsheet (called KU MR Attendance), and I need to see if they are meeting the…
-
Sales pipeline with sort (need child/parent row stays together)
I have a sheet that has child/parent rows and which I can sort by dropdown list order (see below pics) So here are my questions: 1.) how can I have a report where it only shows the current user AND that the child/parent row are together when sorted in the report 2.) I want the sort to be "sort by dropdown list", is that…
-
COUNTIFS Fomula
I created a COUNTIFS formula in Excel which would count the values "Expired" and "Overdue" and add the total's together. Excel Formula: =SUM((COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Expired")),(COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Overdue"))) When trying to emulate it in Smartsheet, I get a #unparseable error. Not sure what I am…
-
Time Tracking - Hours and Minutes
I was working on a way to track some task hours and minutes... came up with this: One of the problems I ran into is that if you put "00" (double-ought) or any minute with a preceding zero, it would error. You can see the work-around I made, not the most elegant, I admit.. If Smartsheet could ignore preceding zeros, it…
-
Pulling Multiple Rows of Data into 1 Cell
Hello, Does anyone know how to pull multiple rows of data into one cell? I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell. I'm thinking vlookup: =VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20",…
-
Avoiding #INVALID DATA TYPE when cells are blank
Hello, I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message. There are 2 formulae set up within the sheet: Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service] Taken (the…
-
=COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN()) Formula help!
Currently using =COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN()) with check-boxes as a quick look to tell how many deliverables have currently been accounted for. I need to add additional lines under the parent row, but don't want them to count toward the total shown at the top. Is there a way to do this?
