-
Between Dates Formula Referencing Another Sheet
I have two sheets I am trying to tie together. Here is the scenario: Sheet 1 (Below): Has the city reference. Current Rent is where I am trying to type the formula. Idea is to return the current rent based on today's date and the city. Sheet 2 (Below): Has the rent schedule range and the city to reference. There will be…
-
How to add a value from any row that has column = May
This sheet (displayed to users as cards) moves projects to different months for capacity planning. We then have a header task for each month which we display the total job value currently assigned to the month. This value is currently manually calculated. How/can we have a formula in the header task that sums all the $…
-
Ignore Summary Task When Counting Late Tasks
I use the formula when counting late tasks: =IF(ISBLANK(Finish107), "", IF(AND(TODAY() > Finish107, [% Complete]107 < 1), 1, "")) The issue with this is that it also counts summary tasks which really aren't tasks themselves. Is there a way to ignore summary tasks so I don't have to manually delete the formula in the cell…
-
Why is a simple division formula not working?
I am trying to divide two different cells into another cell. I am taking my information from my quantity column (qty3) and my rate column (rate3) and divide them. I want it to look like this =QTY3/RATE3 but it always comes up #INVALID OPERATION. I also changed the symbol to addition and it did not add the numbers…
-
Conditional cell linking?
Is cell linking based on conditions possible? I can't think of any other way to get the data pulled to another sheet that I need. I need a specific value pulled from one sheet to another if 2 conditions are met. I assumed and IF/AND statement would just pull the value I need, but I get an error. I'm drawing a blank on how…
-
Status Updates
Good afternoon gurus! Here is our question of the week! I have a process that follows a material through different stages of release. This process spans across multiple sheets, some sheets containing multiple steps in the process. I had a "status" column requiring manual updates, but time has shown that automation would be…
-
How to make a COUNTIFS Formula work
Hello, I'm hoping someone might be able to help me make a COUNTIFS formula work. Some background... I've set up a sheet that aims to capture a team's availability of staff in any given month, should their be a major event occur. The staff would complete a form that asks for their name, contact details, job role and their…
-
SUMIF or SUMIFS and how to use
I need to obtain a running total of days for an inspector for a given month in a column “Days Per Inspector”. I have the following Sheets & Columns: Inspector (Personnel) Sheet Days Per Inspector (SUM – Number) Inspector Contact (Name – Contact List) Inspections Sheet Inspector 1 (Name – Contact List) Inspector 2 (Name –…
-
Calculating elapsed time in percentage
I am looking for a formula to help me with elapsed time in % complete based on date columns. This is the formula I have used and the name of columns. In excel we use DATEDIF but I have tried different ways of doing it. It may be I don't need all the columns. Please help. =IF((COUNTIF([Projected Start Date]3:[Step…
-
=NETDAYS formula updating in the afternoon
I am using the formula below to count the number of days since an item was approved, to today. For some reason it updates in the afternoon. I believe the update happens at 3pm cst. It makes the data look unreliable because it feeds a lot of metrics. For example, it feeds a metric for # of items opened in the last 7 days.…