-
formula question
Hi I am struggling with a formula to calculate a number of fields and looking for some help. I want to calculate the number of both estimated and actual hrs for each month for each client, I have tried the below, but I cannot see where I am going wrong, I have tried COUNTIFS also and cant get it to work.…
-
How can I document the history of Open/Closed Items on a separate sheet.
Summary: I have a sheet of data that pulls a report of items that are open. Each row on the master data sheet hold equipment data, then holds a multitude of columns split into (3) stages. (See sheet example below) The column that lists the open items for each piece of equipment is set up with the following formula to…
-
COUNTIFS formula not functioning how I expect
Hello, I am expecting this formula to count the number of cells in the "FND Prepare" column that have a certain name in them, where the cell on the same row in "Prepare Status" is not green. =COUNTIFS([FND Prepare]:[FND Prepare], HAS(@cell, "NAME"), [Prepared Status]:[Prepared Status], (@cell <> "Green")) Does someone have…
-
"Invalid Value" result from Index-Collect formula
Hi folks! I'm having difficulty with an Index-Collect formula resulting in "Invalid Value" error. I've simplified the formula to the absolute basics to try to isolate the problem. Here is my current formula: =INDEX(COLLECT({YTD-ActualsByMonth_Video-PIDs: Contractor Cash}, {YTD-ActualsByMonth_Video-PIDs: PID}, [PID…
-
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…
-
I want to duplicate a cell value based on a date action
For example, every Monday at 7:00am, I want the value of a cell ( C1) to be "duplicated" and overwrite the value of another cell (E1) on the same row. I want this to effect the entire sheet for these columns. The idea is that column C tracks the "Actual %" of a project and column E tracks the "Previous Weeks %". Column E…
-
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…
-
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…