-
Prorated Rent Schedule Formula Help
Need a formula to calculate what is due per calendar year when the rent amount changes year to year (midyear) Any help appreciated! 06/01/19-05/31/20 Total Rent Due: $175,000 06/01/20-05/31/21 Total Rent Due: $200,000 Total Rent of 2019: Total Rent of 2020:
-
Using IF formula to set conditions for RYGB ball color
Hi there! I have been trying to set conditions with a formula to change the color of a RYGB ball based on two columns, the percent complete and the due date. I wish to use the RYGB balls and not just change the color of the cell, which is why I'm using formulas instead of the conditional formatting. I keep receiving the…
-
COUNTIF with Dates
I am trying to use COUNTIF referencing two Dates columns. The first "Due Date" column has dependencies. The second "Dates" column is simply a list of all possible due dates from the beginning of the project to the end (with no dependencies). I am trying to create a third column called "Total Due" that counts how many tasks…
-
Formula Help - Automating Status
Hi there, I am wondering if its possible to use a formula to automate a status column. For example, when someone submits an intake request through a form and a new row is added to my sheet, is it possible to have the status automatically change to "Intake"? Further, when someone approves the intake (i.e. Laura Approval…
-
Can't identify my Formula error
Can anyone suggest what is wrong with my formula? I get an #INVALID OPERATION result. I've looked at it for too long and can't find the issue. Thanks. =IF(NOT(ISBLANK([Order Date]1)), "Ordered", IF([READY TO ORDER]1 = 1, "Ready to Order", IF([CPM Complete]1 = 1, "CPM Need Approval", IF(NOT(ISBLANK([Vendor Quote requested…
-
Looking for Formula Help
In my sheet, I have two columns. One entitled "Hotel Check-In Date" and the other "Hotel Check-Out Date". So someone might input: Check-in 11/20/19 and out on 11/25/19. I know have individual columns for each date: i.e. 11/20, 11/21, 11/22, 11/23, etc. I am looking for a formula that will be a value of "1" in the correct…
-
Formula Error - Sumifs
Hello, I'm trying to build out a SUMIFS formula that adds up timesheet data from a separate data sheet using 4 criteria: employee name, project code, start date and end date. The formula works fine if I don't have criteria for project code. As soon as I add another criteria to match the client code, I get an #INCORRECT…
-
Remove Symbols
OK, so, we have an update form that goes out to about 30 people. All they have to do is add the latest figures and click 'Send'. That's it. However, they insist on adding in symbols and text, no matter how strenuously we explain that they shouldn't. All they need to do is enter 55 not £55.00 or 0 not N/A. But they persist.…
-
Zapier and Formulas
I'm trying to use Zapier to input formulas into a Text/Number column (which does support formulas) and it comes over as a text string: '=INDEX(PARENT(), 1) I'm using Control Center which creates new rows on a sheet via cell links, but the auto-fill function doesn't seem to work when the new rows are added here; hence…
-
Circulatory Reference - checking if ISBLANK
I need to check if a cell is blank and if it is blank I need to populate it with data from another cell. When I say =IF(ISBLANK([current cell]1), today()) It references it's own cell. Any suggestions?