-
Adding a Column in a Cross Sheet Reference Range on VLOOKUP
I'm exploring changing several of my more complex Smartsheets to rely less on links and more on data tables that are accessed by other sheets using VLOOKUP functions. Everything was fine until I needed to add a new column on one of the data tables and discovered that the named ranges that use that table don't automatically…
-
Formula for # of Room Nights omitting false data
Hi, I have this formula in excel that I am trying to recreate in SmartSheet: =IF(OR(H7815=$D$1,I7815=$D$1),"",DATEDIF(H7815,I7815,"D")) Criteria is Check-In Date and Check-Out Date. The $D$1 is 12/31/9999 which I want to count as 1 night. When that date appears it is in both columns so it produces an Invalid Operation.
-
Formula assistance: adding 12 weeks to a date.
Hi, Can anyone please help me setting up a formula that add 12 weeks to a date. Say if in my main Smartsheet I have populated 10 different dates then I want my "stats" sheet to have a count if formula that adds 12 weeks to all the dates.
-
Combining AND & OR in a formula
I have a sheet with the following three columns: --Was the question asked --Answer 1 y/n --Answer 2 y/n I want the formula to turn the flag red (cell already formatted for flags) if the question was asked, and either answer 1 or answer 2 were no (aka, i need all the no's from everyone who was asked the question). So far I…
-
Can't update permissions on a WorkSpace.
I've created three different WorkSpaces and have a core group I shared them with. In each Workspace is a set of sheets we use as a template for our new projects. I need for that core group to be able to right click on the set of sheets and copy as new in that WorkSpace. I figured out that they need to have Admin level…
-
Using Join with Logic
Is there a way in Smartsheet to combine the data in the rows below AND include the Column Headers AND exclude the "No Impact" Status? The end result should look something like this:
-
Index/Match? Not sure best function for my needs
Hello, I have a very extensive sales tracker that I use to log charges and calculate the sales rep's commissions based on those charges. The commission percentages change depending on a variety of scenarios, so I created four columns with separate formulas for each scenario. I need the commission column to auto-populate…
-
COUNTIF HELP W/BLANK CELLS
I am trying to count the number of employees that have access to work remotely and have not been furloughed or laid off. The columns I'm looking at are: MODIFIED STATUS & TELECOMMUTER. The values in Modified Status are: Furlough, Separated, Reduced Salary, Reduced Hours & Other. The values in Telecommuter are Permanent &…
-
Working to pull data in from four sheets to a master rollup sheet and keep getting an error
#incorrect argument set =COUNTIFS({XXXX Assigned to}, Primary7, {Status}, <>"Complete") + COUNTIFS({XXX Liquid Assigned to}, Primary7, {Status}, <>"Complete") + COUNTIFS({XXXXX Assigned to}, Primary7, {Status}, <>"Complete") + COUNTIFS({XXXXXX Product Range 2}, Primary7, {Status}, <>"Complete") I'm using a Smartsheet…
-
Help with Formula for Data from 2 Columns
Help! Trying to display on dashboard specific date from two columns. The Internal / External Column has two drop down options namely Internal and External The Tier Column has six drop down options namely Office of CEO, HRC, Tier 1, Tier 2, Tier 3 and Tier 4 I can't get the formula to work that will show for example how…