Formulas

For years we have used a contact column to gather emails via forms which allow us to send emails but also lookup in other columns supervisors, etc by calling another directory sheet with the user email. Typically, if you try to type the email address in via the sheet, it will auto recognize the internal contact and place…

Hello. I am currently making a form to track events associated with current locations. These locations will be referred to by their unique, 5 digit ID; however, this form will be used by many different people in and outside the organization, so I would also like to display the physical location so people can double check…

I’m trying to create a reliable Submitted Date column based on the Smartsheet Created Date system column. The goal is for Submitted Date to capture the correct calendar date when the form was submitted and then remain unchanged. The issue is that the Created Date timestamp appears to be displayed or evaluated differently…

I have build a email validation in my sheet where entered emails from a form are entered in a contact field. another field verify the address and validades using the following formula: =IF(FIND("@xxxxxxx.com", [Student eMail]@row) = 0, 0, 1) if the checkbox is triggered (=0), then the registration is valid, and the the…

I have a 6 column, 10,000+ row sheet that feeds into other sheets of data through vlookup formulas, that I update on a weekly basis with new information from an outside source. the columns stay the same but the row #'s vary. is there a way I can upload the new excel version of the data to automatically upload the existing…

I'm trying to create a formula in sheet #1 that: Looks up all rows containing a certain email address in sheet #2 References a certain value in those rows (the values in question are themselves average formulas) Takes the average of all those specific values Displays that average back in sheet #1 I know there are formulas…

I came across some weird COUNTIF functionality recently in one of my duplicate check column formulas. I wrote some exploratory formulas and wanted to share! Key Takeaways Numbers with leading 0s requires both = and "" Regular text requires "", but = is optional Regular numbers do not require = or "", but work with one or…

I've concatenated cells (in Figure 1) by using the formula in Figure 2 (MM stands for Month in my formula), which gives me a viable date (see Figure 3) to work with in SS. However, I want to go down to at least the minute. Is there another formula that can reach down to the hour & minute, or is (DATE(YYYY,MM,DD) the only…

I've been using multiple sheets and cross-reference formulas for about 3 years. Suddenly today, the cells aren't populating?? For example: =IFERROR(INDEX({TBMB Booking Account Executive}, MATCH($[Auto#]@row, {TBMB Booking Auto Number}, 0)), "") This used to display the contents of a cell from another sheet if it matched a…

I am trying to make a formula that looks in a column on Sheet A which is a drop down containing all values from a column on Sheet B, finds the match, and then looks in the row in Sheet B where the match is found to find an additional piece of information and pull that back into sheet A. I am thinking I need indexing and…