-
Help With Collect Function
I have a sheet that has three columns - DueDate, Status and "Status by Color" respectively. Based on the entries in the status column the "Status by Color" cell is assigned the color Red, Green or Blue. My need is to identify the 5 oldest 'overdue' dates within the DueDate column. I achieved this using the following…
-
Can I use formulas to pull in hyperlinks from other sheets?
I want to use a formula to pull in hyper-links across formulas. I started with: =IFERROR(INDEX({Client Intake - Client Invoice Log}, MATCH([Client Name]@row, {Client Intake - Client Name}, 0)), "") but this only pulls in the text of what is in my client intake sheet rather than retaining the hyperlink to the sheet.…
-
Update Sheet 1, Based on Submission to Sheet 2
I have a quick question. I have 2 sheets (Sheet 1 and Sheet 2). In Sheet 2, users submit a form and a field (which is hidden) is automatically checked indicating it's been submitted. What I'm wanting to do is - essentially - notify Sheet 1, that a user submitted something on Sheet 2. My first thought, is to use a VLOOKUP…
-
Formula
Having difficulty with a formula setting task state (Not Started, In Progress, Complete, On Hold) depending upon how the columns on either side are set - Status/RYGB and % Complete. =IF(AND(Status@row=“B”, [% Complete]@row =1),"Complete”,IF(AND(Status@row= "G”,[% Complete]@row=0), "Not Started”, IF(AND(Status(G,Y,R)@row=…
-
Is there a way to COUNTIF checked boxes, but have the formula skip over boxes with an error?
I have successfully implemented a formula in a sheet summary that COUNTIFS checked boxes in a column but because of the nature in which this sheet is being used, some of the checkboxes that I am counting have temporary #NO MATCH errors due to information not yet being available from other sheets which is thus causing the…
-
Vlookup functions not working on a Master Spreadsheet
My colleague and I, who do extensive work on a master spreadsheet that pulls data from many different sheets, are having a lot of trouble with the vlookup function on this particular sheet. We use the vlookup function in multiple different columns on the spreadsheet in question, pulling data from a different source sheet…
-
Weeks to Date Formula- Revision Needed
Hi All, I am currently using the following formula to calculate the weeks until my project delivery date. =INT(([Due Date]@row - TODAY()) /7)) We deliver the project on Tuesday and count that week as week 0. How can I modify this formula so that it reflects the final week as week zero as opposed to the week prior? Any help…
-
Formula Query - Is this possible?
Good morning Smartsheeters, I am using a sheet for my production team to log their hourly product inspections on. Basically, they will complete a form that will then log onto the spreadsheet; this works great. What I would like to do however, is to save the operators time, have the first 2 columns, "part number" and…
-
How to include formula to perform a count from the keyword in the row and update the count?
Hi expert, Not too sure if this is supported within the function. I have a used case below, I need tester to input their test result in name assigned in the column (Col E-G). Once they input the status = "P" or "F", the Column 'Total Pass' and 'Total Fail' will do the count base on the condition keyword P or F in each row…
-
Is there a list of formulas? or samples?