-
Cell link from another sheet based on conditions
Hi all, I hope someone can help as I've been racking my brain trying to work this out! I have multiple sheets that work together of which the data changes each month (it's based on electricity bills with varying monthly kWh (in pence) rates). I essentially want to link a particular cell from one sheet to another sheet,…
-
I need to calculate past due items with 2 column dates
Hi I need to create a formula that help me to calculate the past due items but using to columns with dates. One is the the first column with the "Expected closure date". This will be the primary column to check or validate. But we have another one called "Amended closure date", that will have another date that probably…
-
Index/Collect Formula Error
Hi, I am receiving the error "#INVALID VALUE" on only the first row of my data and wondering if someone could help with where I went wrong in my formula? =INDEX(COLLECT({2021 KPI Scores Range 1}, {2021 KPI Scores Range 3}, @cell = [KPI #]@row, {2021 KPI Scores Range 4}, @cell = Region@row, {2021 KPI Scores Range 2},…
-
Symbols for Status
Why are my words not translating through to symbols? The formula seems to work since its returning the text, but ist converting it to symbol form =IF(Status@row = "Assigned / Not Started", "Empty", IF(Status@row = "In-Progress, On-Schedule", "Green", IF(Status@row = "Delayed", "Yellow", IF(Status@row = "Complete", "Blue",…
-
Increment Auto number column based on check box or other trigger
I'd like to set up a check box or some other trigger that tells the Auto number column to do its thing. This will NOT always (or usually) be a new row and it will be a row with Children (and it's a Child too). That's why I'm looking for a trigger to increment the auto number. Can this be done? Jeana
-
Setting a date column to the 1st of the Month
I am collecting data for a report and the data i want to show is to include everything from the previous month. At the minute i manually change a date column to the 1st of the current month so that it filters out everything that has been added this month. I have found this formula =MONTH(TODAY()) + "/" + 1 + "/" +…
-
Formula help to match contains
Hello! I have 2 sheets: Sheet 1 contains [UserId] column Sheet 2 contains [Assosiated User] and [Machine Name] columns I need to Use [UserId] to look up if [Associated User] contains text to match [UserId] and return the value of [Machine Name] at row Just cannot seem to figure it out. Any help is appreciated!
-
Sheet linking and URL display
Currently, I know how to link to a URL using the right click method on a cell. This also allows a URL to be covered by a "Display Text" Can the same thing be accomplished by entering a string into the cell, similar to how it is done in HTML? This would allow for more efficient linking and design possibilities within a…
-
Complete an action based on matching data in another sheet
I would like to create a formula that will complete an action in one sheet if it see duplicate data in another. For example I have sheet One and sheet Two. If both contain the same name in the [Name] column. I would like to indicate that somehow in sheet #1 either by highlighting a row or using a flag or symbol or even…
-
How do you calculate overdue dates?
Hi, I am creating a sheet to maintain our fleet. I have a column with the registration and a column with the Tax and MOT due dates. I need a formula to calculate the number of dates that are overdue for the summary but everything I try isn't working. Does anyone know a formula for this? Thank you in advance.
