-
Auto drag down TODAY formula
Hello! I have a sheet with a column designated to always show the =TODAY(0) formula. However, I noticed that when new rows are added, I have to manually drag down the formula. Is there a way to auto-populate the column and lock it so each new row will always have this formula? Thanks!
-
Format cells in sheets and reports that have an update request
Has anybody found a way to highlight (change cell color or format) a cell or row in a sheet, once an update request has been manually requested by a user? Additionally how can the same highlight be extended to reports built off of that sheet? We have users manually requesting updates to a single column in a report, but as…
-
ISBLANK Formula
Is there a limit to how many nested IF statements you can have in one formula? I have six columns and trying to pull data for dashboards based on the content in each column. Only one column will be populated with data depending on the project plan type. But I cant seem to get this formula right? Can anyone see my error??…
-
How can I search another sheet for a name match within a certain date range?
I'm struggling to create a formula that will not only look for a name match from one sheet to another, but it will only return a match if the date on the row was within the past 6 months. For finding a name match between sheets, I've been utilizing the following formula: =IF(CONTAINS(Contractor@row, {Change and Offboarding…
-
VLOOKUP to Return Two Users
Hi! I need a formula that looks at two columns and returns an email address based on vlookup if one of the two columns is blank and two email addresses (again based on vlookup) if neither column is blank. The formula works well when one of the columns is blank and only one email address is returned. The problem is when two…
-
Is there a way to simplify this formula?
I have 3 columns with dropdowns of 'Complete' 'In progress' and 'Not Started'. In a fourth column (Percentage of Task Complete) I have a formula that assigns a value to each of those text items and then does an average and converts it to a percentage. Here is the formula: =(SUM(IF([Step 1]@row = "complete", 100, IF([Step…
-
Per diem interest rounded to nearest cent doesn't translate to accumulated interest?
Happy Thursday!!! I've got a sheet that tracks loans to generate statements and payoffs. For the most part, it works GREAT. The "Holding Cost" (per diem) column is run by =[Loan amount]@row * interest rate / 365 Since the column is set for Text/Number & Currency, it automatically rounds to the nearest cent which is perfect…
-
IfError with Index and Match
Hi all, I am trying to troubleshoot a formula that has worked for me in the past and wondering if anyone can look it over. Typically this formula is used for a travel grid that denotes when someone is Work/Travel Out (X/T) or just Travel Out (To). Happy to share the sheet itself as well to look at further.…
-
Contract Managment set build assistance
Has anyone created a contract management set that links all variations/milestone requirement & payments/ reporting and review dates. The contract set in the solution centre does not suit my office size or way we work. I don't want the top-level source sheet to be bogged down with too many details so i need the underlying…
-
Help with REPLACE formula
Hello, I'm trying to implement some form of auto-fill functionality in my schedules using the REPLACE function. I've attached an image of what I'm trying to accomplish. In the example I'm trying to auto-replace the "BRAND" text in subtask 3 and 4 with the "NIKE" text in line 2. The idea being that I have a schedule that…