-
#Invalid Data Type with VLOOKUP
Good day everyone, I'm trying to use the formula VLOOKUP to pull some information from another sheets, but it shows me #Invalid Data Type. I have this sheet named "2020 vs 2021 P/Month" and I need the formula to look for revenue on sheet "Rev By Month P/BTC 2020", so when I fill this last one, fills up the information to…
-
Licensed vs Admin status
Does anyone else have a challenge with this mix of functionality? As a "free user" with admin access to a sheet, someone can move or copy entire rows somewhere else. But that same user cannot lock a row or columns. (And, while I am at it, there is no workflow that allows programmatic locking of columns). (This became an…
-
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",…
