-
Creating a Unique ID (YY-###)
I'm looking to create a unique ID per entry as YY-### that will reset each year (24-001, 24-002, 24-003, 25-001, 25-002 etc) Row ID: (auto number column) YYYY: =YEAR([Date Submitted]@row) YY: =RIGHT(YEAR([Date Submitted]@row), 2) Entry ID: =YY@row + "-" + IF(COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row)…
-
Using IF with CONTAINS and AND for multiple columns
I am looking at two different columns - one is called Status and the other Priority. I would like to have a formula/function setup that will look for "Open" within the Status column and "High" within the Priority column. If it finds "Open" and "High" together in any row, I want a "True" value returned. This will be used in…
-
Advanced VLOOKUP
I have a sheet 1 having column object name as ZCONXXX (where XXX are numbers) and another sheet with task name having tasks for execution like “Review and upload ZCONXXX” and I need to update duration in sheet 2 matching object name column in sheet 1 to task name column in sheet 2. Help please
-
IF level@row = X, Display short version of dashboard link, else calculation
Hello Community, I have a series of columns that are summarising the output of complex projects. I would like to be able to have a "Link to Dashboard" at the top of a column that is a column formula. The link would provide the end user with instant access to more detail if required. Ideally if anyone has a solution, the…
-
Using IF with Find
Hi, I'm trying to simplify a cell's contents, to allow better cross referencing and help eliminate issues with typos. I create cells with a serial number, someone requests that serial number though a form. Since the serial number can have a lot of variations requests through the form can have typos leading to the cross…
-
Updating one formula reference is updating all formula references.
I have several formulas that reference a different sheet. When I update one formula it updates all other formulas that reference that sheet regardless that I've pointed them to different sets of data on that sheet: For example - the above formula references the sheet Lafayette Square but the column with dollar values that…
-
How would I add a MAX function to COUNTIFS formula?
ISSUE 1: I have a formula I'm using to update a radio button called "Paused" to identify items in another sheet that are paused. I need to make sure it looks at the most recent occurrence of the item in the list using the last run date. How would I add a MAX function to this formula using the last run date.…
-
How can I get this formula to return a value when two criteria are met?
Initially, I used the formula below to return the Payment Term value from my Contract & Exhibit Tracker where the vendor #s match between my source sheet and output sheet. And it worked. =IF(ISBLANK([Vendor #]@row), "No Match Found", IFERROR(INDEX({Contract & Exhibit Tracker - Payment Terms}, MATCH([Vendor #]@row,…
-
COUNTIF reference not working
I have a time entry on one sheet where times are entered in the HH:MM (24 hr) format and another column that pulls the hour of that day. [Time Received (FORM)] - time in HH:MM format [Received Hour of Day] - pulls the hour of the day with the following formula: =IFERROR(LEFT([Time Received (FORM)]@row, FIND(":", [Time…
-
Nested(?) If statements
Not sure if nested is the right term. I want the formula to show - If any of these columns (IT, SUS, HSE etc) have "Not Achieved" in them then look for the largest/ latest date from the columns IT comp. SUS Comp. HSE Comp. and display that date. Otherwise "Badges complete". The result column is a date column so not sure if…
