-
Translating an Excel Formula to Smartsheet - #UNPARSEABLE
Hello all, I'm attempting to create several self-updating sheets fed by a manually updated database sheet. The goal is that it will feed into the multiple other sheets so that we can then give access to smaller groups of people based on which locations are returned by the reference cells. I've been able to successfully…
-
Index Match Formula Not Working
I have a customer ID table (sheet 1) with two columns, Customer ID and Customer Full Name. On a separate sheet (sheet 2), we have entered customer projects using the same column, "Customer ID". To prevent double entering and typos, I have a column on sheet 2 I have a column for the customer's full name and I use an index…
-
VLOOKUP or Match Formula for cross referencing sheets
I'm trying to create a report that outlines tasks by Department. When we created our project plan sheets, we didn't include a department column and only included an Assigned To column for resources. I do however have an Employee Info sheet that lists all of the employees who can be Assigned To and their corresponding…
-
How do I create one sheet that automatically ingests data from other sheets?
Howdy community! I have about 90 different project plan sheets that there are static cells I'd like to pull information in from. Those I have already created on a different sheet by using a cell reference. Easy enough. My struggle now is there is a separate sheet for all 90 of those projects that contains status updates.…
-
I need a formula to calculate a YTD average # of employees that will change as the year progresses.
=AVERAGEIFS(Jan3:Dec3,">"&TODAY-365,Jan3:Dec3,"<"&Today) - this gives me the #UNPARSEABLE message =AVG(Jan@row:Dec@row) / (MONTH(TODAY())) - this gives me another incorrect number I have tried several others without success also. The number is manually entered each month and the total should change each month.
-
Is there a formula to add check boxes from a row?
I have a form, where the form user is checking click boxes. I am trying to total the number of check boxes so I can total their entry quickly. Is this possible? I have tried adding columns to give the check boxes a value, then tried counting those added columns. I am at a loss, and feeling like it can not do what I am…
-
Date Formula to return following Monday or Wednesday
Hi everyone, I need help with another date formula. For this one, we have a system entry that needs to be done which will trigger the "Expected live date". I currently have a "Task" column and a "Due Date" column. Here's how it should work but realize the timing may be impossible to capture... 1) If the system entry was…
-
Exclude one child row in the parent row update
I have created sheets to track critical HR tasks that are performed on regular basis. All is working as expected (thanks to help from a few of you) but this morning was thrown a curve ball. USE CASE: This one tasks has ten check points in process. Of the ten, the last step is only late 4 business days after the previous…
-
Formula to Auto assigned Id_Number as Input in Sheet
What Formula should I used get desired JV number output -> Here In Sheet I what to get out similar as shown in JV Number column, Point to be note number id in JV number should be assign as per entry in sheet. 1) So Here in variation# column the entry is against variation num. VAR-0012814 so number assign to it should be…
-
Getting #INVALID VALUE error with INDEX/COLLECT function
I'm trying to pull distinct values from a subset of a range. I'm using the following formula: =INDEX(DISTINCT(COLLECT({Subject}, {Site Number}, {Metadata Site Number})), [Unique ID]@row, 0) I'm looking to generate a list of distinct values that are for a specific subject (patient) that's at a specific site, which is…
