-
Pulling Multiple Rows of Data into 1 Cell
Hello, Does anyone know how to pull multiple rows of data into one cell? I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell. I'm thinking vlookup: =VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20",…
-
Avoiding #INVALID DATA TYPE when cells are blank
Hello, I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message. There are 2 formulae set up within the sheet: Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service] Taken (the…
-
Auto check box using =IF(Primary@row="California",1,0)
This formula works as typed. However, I need your help writing this formula such that it will auto check box if the word is "California", or "Florida", or "Arizona".
-
Set RAG based on % Complete & Deadline Date Proximity
Hi I am trying to get my formula to set a RAG status based on the % complete and deadline date proximity. My formula is: However, the last entry with a deadline date of 12/11 show be showing a "Yellow" RAG but, it still shows "Red". What have I missed? TIA Cheryl
-
Proper Syntax for IF and VLOOKUP
I am trying to create a formula that will run VLOOKUP if a cell is blank This doesn't error but it leaves the formula field blank =IF((USERR3 <> ""), (IF((VLOOKUP(SDR3, {SFDCUsers Range 1}, 2, false)) = 0, "No", "")))
-
Reference Another Sheet without Function
We use "Reference Another Sheet" quite a bit in our sheets. We do this by starting a function in a cell, then putting an open parenthesis, then clicking on the link in the resulting dialog box that reads "Reference Another Sheet" . This works fine. The thing I'm running into is sometimes I want to reference another sheet…
-
COUNTIF with OR statement
Hi! I'm trying to count the number of cells from two different check box columns (cross-sheet reference) which show a check in either one of the columns. I'm getting an UNPARSEABLE error. Could you help me out with this? Thanks!
-
Using COUNTIFS with AND
Hi! I'm trying to count the number of items which have one status as "Operational" and another status with any option EXCEPT "Operational". This is the formula I have but it is getting an error as UNPARSEABLE. Note that I'm using a cross-sheet reference. Thanks!
-
#UNPARSEABLE using COUNTIF and cross-sheet Reference
This feels like a dumb question. I'm trying to get a count of total "To Do" items in the entire Status column from the Team SCRUM: Communications sheet. This function is working fine without the cross-sheet reference, but nothing I've tried is working to correct the issue. The formula is as follows: =COUNTIF({Team SCRUM:…
-
COUNTIFS with Date range with @row
I have the current formula that counts the start date but I wanted to see if i could do a range of dates from start date to end date and count those. Any Advice? =COUNTIFS([Staff Team]:[Staff Team], [Staff Team]@row, [Start Date]:[Start Date], [Start Date]@row)