-
Complex Lookup Formula - need help with returning the proper values.
I'm working with a sheet that is being uploaded daily with new information via Datashuttle. The sheet is populated with the [Route ID], [Warehouse Code] and then in turn i'm trying to auto populate the [Assigned Planner]. The warehouse code has both the warehouse of pick up (which is where the planner physically sits, so…
-
How to identify row number in a sheet where new rows will be added
I want to create a sequence of 1, 2, 3, etc. in a column, but challenge is new rows will be added in this sheet. So, when a new row is added, the top row must have value of 1, followed by 2, 3, 4 for other rows in the column... wish if there was a formula like ROW(), like we've in excel. Any suggestions how to accomplish…
-
Counting blank cells in a report
I have 23 sheets and need to set up a report that includes count of the number of projects. I summarized the project name column and chose count, however it is counting the line that contains the sheet heading. How do I have it NOT count that row in the report? Thank you,
-
Formula to capture Quarter
Good Day! I am struggling with a formula. I would like the formula to identify the quarter within which the project will be delivered and fill the column Quarter. This information will help our finance department plan revenue. I tried the nested IF function below, and I had the ‘’UNPARSEABLE error message. =IF([Date…
-
Dynamic Date Validation and Computation
Hello! I'm almost embarrassed to post the following because I recognize it as a horribly constructed formula, but I am experimenting. First, let me highlight my use case: I have a sheet (first screen shot) that pulls in Start/End Date, Hrs/week, and Hourly Rate via INDEX formula. I'm attempting to have the sheet…
-
AVE or AVERAGEIF when refrencing another sheet
I have my master sheet set up with a duration column. I've been trying to build a formula on my metrics sheet that will allow me to average the duration column based on who is assigned that purchase order. I've tried two formulas, one using AVE and the other using AVERAGEIF and both return the #UNPARSABLE error. The first…
-
Help fixing this formula
Note: If the pre-closing draw date submitted to strike Team is blank return ''01_Strike Team Needed', 'If the pre-closing draw date submitted to QAQC is blank return ''02_QAQC Needed'', 'If the pre-closing draw date submitted to TIGR is blank return ''03_TIGR Needed'', If the pre-closing draw date submitted to Strike Team…
-
Checkbox Summaries
Hey all, I am a new smartsheet user using smartsheets to track progress for a rehab project for an apartment complex. We are checking off each completed task for each unit using checkboxes. To best track progress, I am trying to find out how to get totals for total number of checkboxes on the spreadsheet and total number…
-
Stop counting NETDAYS formula based on date in another column
Here are the column names in my sheet that pertain to my question: [Address By Date] [Days Remaining / Overdue] [Date Resolved] The column formula is in the [Days Remaining / Overdue] column and I want it to stop counting when a date is entered into the [Date Resolved] column. The current column formula in the [Days…
-
Dates formula : add several months in a formula
Hello everyone, I have a small question about the use of Smartsheet for a formula on dates. Here is the use case : I have a column with a date. I would like to have another column with a formula that add 3 months to that date. I already try =DATE(column)+90 but it only add 90 days and sometimes 90 days is not exactly a 3…
