-
How to create an Index Match Formula for multiple sheets?
Hi, I am currently trying to create an Index Match function which will be able to search multiple sheets (5 to 6 separate sheets) for the correct information. The destination sheet has two columns that it will look at. A "TIDC" column where staff members place a ticket code An "Assigned to" column, which is a contact list…
-
CONTRACT EXPIRATION DATE
1-I am trying to reference another to provide results of contract expiration date - 2- I would like to display in a dashboard the percent of total contracts that has expidered, will expire in 1year, 6month, 90 days 30 days, 14 days and 7 days 3- finally if there was an extension, to input that date in a new column that…
-
RAG Formula with Reference to Start, end date and Status
Hi, I am aiming to create a RAG status referencing to three columns in a sheets. Wondering if anyone would be able to help me creating a formula that covers the following scenarios: By the Work Start Date: Not Started = Red In Design = Amber In Development / In Testing / Delivered = Green By the Work End Date: Not Started…
-
Index Match Across Multiple Sheets
Hi Everyone, Hoping y'all can help as I'm at a complete loss. I'm trying to pull information into 1 master sheet based on a unique identifier across multiple sheets and it's just not working. I can get it to work for 1 sheet using =INDEX({Status},MATCH([Proj ID]@Row,{Proj ID},0)). Each sheet is for a different team,…
-
How do I prevent a formula from checking an empty row.
I have a formula that checks a box if a name appears on another sheet. However, when there is an empty row the column remains checked. How do I prevent this? In "Previous Student (within last year)" there are check marks when there is no name in the name column. Here is the formula I am using: =IF(COUNTIF({Fall 21 Names},…
-
Displaying Payments on a date with Calendar view ?
I have 4 columns with Payment and 4 with date I want to associate these payments on a calendar. Like this is going to be my format.
-
Formula References vs Lengthy Formula Updates
I maintain a lot of large sheets - at least 9 per FY with around 190 columns and up to 1,500 rows per sheet. Within these are many transit timeline formulas for off-shore shipments. These are often in flux, and when something changes, I need to go through each sheet and update the numbers in the column formulas. This can…
-
COUNTIFS ISSUE
I'm hoping someone can help. I am trying to use a COUNTIFS formula to count only if "Final Verification" is Gravoc AND "Verification %Complete" is set to 100%. I am getting stuck and it gives a result of 0. I have attached a few screen shots below. I have even tried using @cell inserts...
-
Out of Service Formula
Hi Community, I'm attempting to create a formula showing the OOS status of the most recent submission for each asset based on the Asset ID in form submissions. For example, below Asset ID FD-001201 would be considered out of service, however, I'm not sure how to account for it being the most recent submission if there had…
-
COUNTIFS Question
I have a smartsheet with MANY columns; I need to do a countif based on 3 of them. This portion works: =COUNTIFS({Quarter Created On}, "Q3", {GEO}, "AMERICAS") When I try to add this, it all falls apart: {EA 2.0 Architecture Selection}, FIND("Collab") I tried this just to see if I could get the right count of rows with…