-
Does "You can only reference a total of 100000 cells from other sheets." apply to 1 or all sheets
I have 2 sheets consolidating data from 2 source sheets. One of my consolidation sheets works fine, pulling data from both source sheets. The second consolidation sheet fails whenever I attempt to change the sheet reference in some cells to point to the second source sheet (which I was able to do on the first consolidating…
-
VLOOKUP to calculate dates - holidays = due date
My Eval Pull date is based upon the TCP Type the user selects and also needs to calculate in the holidays. Currently, Eval Start Date has a reference to another cell =[Date Completed]32 Once the Date Completed/Eval Start Date has a date, based upon the users pre-selected TCP Type of 115 days (VLOOKUP to TCPTypeDropDown and…
-
Month from Date column
Good afternoon! I think I may be overcomplicating this. With the below, I'm trying to pull the month based on A. However, as it was a drop-down for the purposes of the form, I copied it as a date column in B. I included a legend of what is what and what I'm trying to get is the formula for D through F to work. What am I…
-
Need help with a sumifs formula
I need to figure out why my "sumifs" formula is not working. I want to total a duration column if a payroll Item column contains one of 3 possible choices. My formula is as follows... =SUMIFS(Duration:Duration, Payroll Item:Payroll Item, "Hourly",[Payroll Item:Payroll Item, "Overhead Hourly"], [Payroll Item:Payroll Item,…
-
7 Day Average/30 Day Average
I am trying to build a formula for a data set that I can calculate as a complete average, but I'd like the 7 day average and the 30 day average. Here's the formula that works: =AVG([% Successful]:[% Successful]) I know I will need to use AVERAGEIF, but can not get the date parts correct. Thanks for your help!
-
Reporting on columns
Hi, i am trying to create a report based on user from multiple sheets. The report was working just fine about a month ago and now its not picking information correctly. I named the 2 columns in my sheets the Asset Type and I need the report to grab information based on user name so it becomes one column and my hardware and…
-
Nested IF Formula Issue - Returning UNPARSEABLE
I have a formula that is returning the month name for a specific date column (Next Audit Period) that is returning an UNPARSEABLE error and I can't figure out why. The formula is below. = IF(MONTH([Next Audit Period]@row) = 1, "Jan”, IF(MONTH([Next Audit Period]@row) = 2, "Feb”, IF(MONTH([Next Audit Period]@row) = 3,…
-
IF statement within an Index Match
I would like to have a formula on one sheet that looks to another sheet for the following. On one sheet it lists all the employee IDs with a "Module 1 Date" column and/or a "Module 1 Makeup Date" - if they missed the "Module 1 Date" (so the employees that missed Module 1 would have two dates ). I have another sheet that…
-
Weekly Sum for Rolling 1 year (52 weeks)
I have a smartsheet grid that tracks the number of units sold each day - every day is a different row. Every Monday, it sums the number of units sold for the past week. For example, on Monday 10/18 it will sum the total number of units sold between Monday 10/11 - Sunday 10/17. This is displayed in a weekly total column…
-
conditional fill a cell with value from another cell in the same sheet
I have a travel request form that user can fill out for self or for someone else. Fields involved are: Requestor Name - person completing the travel form Request for - who travel request is for. Value options are "Self" or "Someone Else" Traveler Name - If request is for "Someone Else" then Requestor types in Traveler name…
