referencing using @row
I've tried multiple searches and haven't found the right keyword/tricky phrase to answer my question.
I am using a form for submissions and trying to calculate how long a trend has been occurring. Right now my working formula is this:
=IF([email protected] = "Yes", 0, IF(YEARDAY(Date1) > YEARDAY(Date2), DaysSince2 + (Date1 - Date2), DaysSince2))
The problem is that if I have to manually enter a row because my operator forgot to enter data, it breaks the formula and I get a whole slew of "invalid data" or "circular reference" errors. I would like to think there is a way to use the @row function to get something like this:
=IF([email protected] = "Yes", 0, IF(YEARDAY([email protected]) > YEARDAY([email protected]), [email protected] + ([email protected] - [email protected]), [email protected]))
Basically this would mean that anytime the formula is used it is referencing the data in the same column, but 1 row below where the information is entered. Is this even possible in smartsheet? I remember being able to do it using the OFFSET formula in excel, but it appears through searches that even after 2+ years of requests for that function it hasn't been built in.
Answers
Manually entering a new row at the bottom of the sheet should not break the formulas and should autofill.
Are you manually entering in the middle of the sheet?
Do you have a Created Date system generated column?
thinkspi.com
I sometimes have to manually enter rows in the middle of the data (ie my operator forgot to enter data for 6/4 but entered data from 6/5 to 6/8).
The form populates data at the top of the sheet so that the most recent information is easily visible without having to scroll through hundreds of previous entries.
I do have a system generated "Created Date" column, but it is not necessarily the date that my operator is entering information. Sometimes that day will be the same (ie entered at the COB before going home) but usually it is entered the next morning.
So you need your formula to reference the row immediately below it?
You can use an INDEX function coupled with a "helper" column that replicates the row number.
The helper column would be a text/number type, and you could use something along the lines of...
=COUNTIFS(Date:Date, OR(@cell <> "", @cell = ""))
Dragfill this down all of the rows and it will replicate the row number so that it can be used in formulas.
Then you can reference this column to look a the current row number and add 1.
INDEX(Date:Date, [Helper Column]@row + 1)
If your formula is in row 1, then you would use the above in place of "Date2".
The only time this will throw an error is in the very last row of the sheet where there is no row below it.
thinkspi.com
I'd honestly like to avoid adding helper columns if possible, but in my short couple months of using smartsheet it seems this is the go-to answer for most problems.
when trying the advice for the helper column I was just getting a sum of the rows, not an incremental amount matching the rows. Had to change it to this:
=COUNTIFS(Date$1:[email protected], @cell <> "")
now i'm just trying to get all the index functions to actually work. so far I'm here and unable to find the error causing the circular reference:
=IF([email protected] = "Yes", 0, IF(YEARDAY([email protected]) > YEARDAY(INDEX(Date:Date, [HelperColumn1]@row + 1)), INDEX(DaysSince:DaysSince, [HelperColumn1]@row + 1) + ([email protected] - INDEX(Date:Date, [HelperColumn1]@row + 1)), INDEX(DaysSince:DaysSince, [HelperColumn1]@row + 1)))
The error is mine. My apologies. Apparently I wasn't as focused as I should have been.
Yes. The $1 and @row references are correct for the row number formula.
To correct the circular reference error, you are going to change which column your row number formula is pointing at to one that is not used in the IF statement.
Sorry for the mix-up. That's what I get for rushing through my post.
thinkspi.com
@Paul Newcome I'm revisiting this as I had shelved it in favor of other projects and my operator hadn't missed an entry, until today.
I've been unable to decipher what you meant when it comes to eliminating the circular reference. I'm guessing you mean that I need to change the "DaysSince:DaysSince" reference, as when I make that a different column then I can make the formula not give me errors. The problem is that is the item that I need to be adding incrementally if the "DidIncidentOccur" is not equal to "Yes". So I'm unaware of how I can keep adding to that column based on the number below it.
any chance you'd be able to type out a version of the formula you think would work?
You need to change the Row Number formula. This one:
=COUNTIFS(Date:Date, OR(@cell <> "", @cell = ""))
To reference a different column.
thinkspi.com
@Paul Newcome I swapped my helpercolumn to this:
=COUNTIFS(Created$1:[email protected], @cell <> "")
This is now my "DaysSince" formula:
=IF([email protected] = "Yes, a near miss", 0, IF(YEARDAY([email protected]) > YEARDAY(INDEX(Date:Date, [HelperColumn1]@row + 1)), INDEX(DaysSince:DaysSince, [HelperColumn1]@row + 1) + ([email protected] - INDEX(Date:Date, [HelperColumn1]@row + 1)), INDEX(DaysSince:DaysSince, [HelperColumn1]@row + 1)))
still getting this:
You need to pick a column for the helper formula that is not referenced in your main formula. I only used the Date column as an example. It needs to be something that is NOT listed in your DaysSince formula.
thinkspi.com
@Paul Newcome I thought that was what i accomplished by switching the helpercolumn formula away from the date column? The main formula now has no reference to a column that is being used to create the helpercolumn numbers.
I missed this part with your screenshot.
Your INDEX formula is referencing the column the formula is in. What is the reason for referencing the DaysSince column in your formula?
thinkspi.com
any chance it's having problems with the index function finding the next row?
I'm trying to separate out what might be the issue. when I type this function into it's own column I get an "invalid column value" error:
=INDEX(Date:Date, [HelperColumn1]@row + 1)
Theoretically if I put that in the first row it should give me the number 2, right?
@Paul Newcome I'm trying to do a continuous summation in that column so that my boss can track how many "days since" an incident occurred. That means that the answer for cell 1 of the "DaysSince" column is dependent on the value of cell 2. day 2 is dependent on cell 3. day 3 on cell 4. so on and so forth.
the operator enters this data on a form:
name, date, did incident occur (yes/no)
if an incident occurred yesterday the value should be set to zero. today if no incident occurred the value should be 0+1=1. tomorrow if no incident occurs the value would be 1+1=2. if saturday is incident free then 2+1. etc.
I have a formula that works * =IF([email protected] = "Yes", 0, IF(YEARDAY(Date1) > YEARDAY(Date2), DaysSince2 + (Date1 - Date2), DaysSince2)) * but if my operator forgets to enter data and I have to manually insert a row, the formulas get broken and do weird things, which causes me to have to drag/copy the formulas down to the bottom of the sheet. right now with a little under 3 months of entries that's not a problem. but if it happens at the end of a year it's more of a PITA, so I was just trying to find a solution that forces the summation to occur with the value in the column directly below it rather than referencing specific cell numbers.
ok @Paul Newcome now i'm really confused.
I broke out the original formula into individual columns for each part. The formulas all somehow work great on their own and match what the old formula would have produced, but combining them into one function doesn't seem to be working correctly and gives the circular reference:
Here are the formulas for each column:
YearDayThisRow: =YEARDAY([email protected])
YearDayRowBelow: =YEARDAY(INDEX(Date:Date, [HelperColumn1]@row + 1))
DaysSinceRowBelow: =INDEX(DaysSinceOldFormula:DaysSinceOldFormula, [HelperColumn1]@row + 1)
NumberToAdd: =([email protected] - INDEX(Date:Date, [HelperColumn1]@row + 1))
update....so far I've eliminated all the specific cell call outs except for one. if I put this formula into cell 1 and drag/fill it works:
=IF([email protected] = "Yes", 0, IF(YEARDAY([email protected]) > YEARDAY(INDEX(Date:Date, [HelperColumn1]@row + 1)), DaysSince2 + ([email protected] - INDEX(Date:Date, [HelperColumn1]@row + 1)), DaysSince2))
so it appears the circular reference is coming from the "INDEX(DaysSince:DaysSince, [HelperColumn1]@row + 1)" portion of my formula.