-
Dynamic Column Reference
Hi! Lets say I have a basic formula like this in a "budget" column: =SUM([Week 1]@row:[Week 10]@row) And I also have a cell in a helper column that includes this formula: =WEEKNUMBER(TODAY()) Is there a way of [Week 10] changing to [Week X] based on the number in the week number cell? I have a column for every week of the…
-
Formula Copy/Paste Issue
I have the below formula to extra a Quarter based on my column "Expected Close Date". The fiscal year begins 10/1. When I copy the formula to other rows it displays the same number (i.e. January showing as Q4 when formula is pasted when it should be Q2. =IF(MONTH([Expected Close Date]@row) >= 10, "1", IF(MONTH([Expected…
-
Is there a way to simplify formulas with a variable within the formula?
I would like to simplify certain formulas that contain common/repeat functions by assigning a variable. This can be done in MS Excel using the LET function (see image below).
-
Formula to split month and year into two columns
I have data coming into a sheet (Month column) that I need to separate into Month (Helper - Month) and Year (Helper - Year) columns. I have worked out how to get the month separated using the LEFT function, however when trying to use the RIGHT function to get the Year separated, it works where the month is 4 or less…
-
Need help with monthly form tracking formula to Master sheet
Intake Form sheet for each site that has submitted their monthly activity form. Entries will be in this sheet for the entire current year, so each site ‘might’ have up to 12 monthly entries. Each of the 60 sites can have multiple entries, the 2 sheets are linked with Site ID (form sheet) Site # Text (master sheet) If the…
-
How do you create a sheet that calculates 60 weekdays minus holidays?
Hello, I am trying to create a sheet that calculates 60 school days from a specific date. It needs to omit weekends, holidays and breaks. I used the following formula and it is still not calculating correctly. =WORKDAY([Document Date Signed]@ros, 60, [PPS Holidays]@row) I entered all of the holidays in the holiday column…
-
Checking two sheets for a match and returning 'yes' or 'no'
I have two reference sheets that I'm trying to connect to another sheet that will serve as a log. How it works: User enters information (VTH Code is associated with a vendor price on both the reference sheets). The user enters the price a client was charged. This is used to check if it matches either of the reference…
-
Collecting Duration a Row is on a Status
Hello, I am working on a sheet where we have several statuses that can go in just about any order. We have a suggested flow, but it's more to help the user read through and make sure they didn't miss anything. I've listed the statuses below for reference. For example, the user could go from 1 to 2 to 3 to 4c to 14 to 16.…
-
Summary formula
Hey I'm trying to do this in a Sheet Summary: If [Date] is today, then show me the value in the same row in column G. This works for an individual row (1) =IF([Date]1 = TODAY(), [G]1, 0) But I can't get it to work with @row to search all the way down the sheet to look for the current date, I've tried these: =IF([Date]@row…
-
Need help with a formula to pull in data with a certain criteria from a column ...
Hey Folks, Need help with a formula to pull in data with a certain criteria from a column in a Sheet source into a Metric Sheet. I'm wanting to only pull in a summary of 2 or more ( 2 = >) Jobs meeting the criteria for any given month. So in the Archive Sheet screenshot below, if rows 294 through 305 were January for Line…