How to create a column formula that adjusts time depending on length of previous row

JJLewis

We are scheduling surgeries and need to adjust start times according to the previous procedure length. I am currently using a Time column and a Length column and a VLOOKUP formula pulling times from another sheet in the background...

=IF(lvl@row = "0", "", IF(lvl1 = 0, "09:00", IF(Length1 = "50", VLOOKUP(Time1, {Time Range}, 6, false), IF(Length1 = "45", VLOOKUP(Time1, {Time Range 1}, 2, false), IF(Length1 = "30", VLOOKUP(Time1, {Time Range 1}, 3, false), IF(Length1 = "60", VLOOKUP(Time1, {Time Range 1}, 4, false), VLOOKUP(Time1, {Time Range 1}, 3, false)))))))

The issue is that if we cancel a procedure, it breaks the formula and I have to go in and re-add a row and replace the formula for that specific row...We are tracking 10 surgeons schedules so having to replace formulas every time we need to reschedule a procedure is very time consuming...Thank You in advance...

Below is the reference sheet which has the procedure lengths and the check-in times used in the formulas...

Kelly Moore

Hey @JJLewis

If I understand your problem correctly, the absolute references of Time1 and Length1 are preventing you from creating a column formula? The screenshot above isn't showing the destination sheet and if your Time and Length references are actually dynamic.

If the references are absolute, you can solve your problem using two summary fields on your destination sheet (Summary fields are found on the RIGHT ribbon of your sheet). Create one field for Time and one for Length. You will point these fields to Time1 and Length1, respectively

Time =Time1

Length = Length1

Then replace the absolute references in your current formula with the the summary field references. In a formula, these have the syntax Field name followed by a #. It is exactly the same if you were writing a column name then instead of row number, use a #.

=IF(lvl@row = "0", "", IF(lvl1 = 0, "09:00", IF(Length# = "50", VLOOKUP(Time#, {Time Range}, 6, false), IF(Length# = "45", VLOOKUP(Time#, {Time Range 1}, 2, false), IF(Length# = "30", VLOOKUP(Time#, {Time Range 1}, 3, false), IF(Length# = "60", VLOOKUP(Time#, {Time Range 1}, 4, false), VLOOKUP(Time#, {Time Range 1}, 3, false)))))))

If this isn't the problem and you're formula is actually dynamic (changing row numbers) we can work through that. A helper column that is indexing the row position will be needed if you don't already have that

Kelly

JJLewis

Thank You @Kelly Moore,

I am not familiar with the Sheet Summary function, more specifically how to point them to the the proper columns.

I am trying to make it a column formula so if a row is removed, it will not break the Time formula...I have included the destination sheet...

Kelly Moore

Hey @JJLewis

In your destination sheet - does your formula ALWAYS point to row #1, or does the row number change (which means it is dynamic)? The summary fields would only work if it was always pointing to row#1.

JJLewis

@Kelly Moore

The row number changes in the formula...

JJLewis

@Kelly Moore

So, to answer your question, yes, I guess the formula is dynamic as each row, Time is dependent on the previous rows Length. I do not have a helper column that is indexing the row position. Thank you for your time on this!

Kelly Moore

Hey

In addition to the numbering columns that I knew were needed, I had to add an additional column to the sheet to create a work-around for the circular reference that develops when you try to apply a formula to the same column that you want the results in. Before, when you manually inserted your formulas, you were specifying what row to evaluate. The new formula calculates what row to evaluate, which means the formula has to assess what row it is currently sitting on. Since that position is exactly where you want to insert the new value the formula creates a circular loop, ie, circular reference. Current smartsheet functionality does not allow you to automatically ignore the circular reference. Because of this, the best I could do was add a column that would show the new time but there will be a manual step of copying the new time into your desired Time cell.

The approach will need to a self numbering column that will always keep the row numbers 1 through whatever, in ascending order. This will provide a reference point to find the preceding row. Often the reference can be done by inserting the system auto-number column - however since rows are being inserted out of order, this one column cannot be solely used. This means two helper numbering columns are required.

1. - add helper column(s) for numbering rows

1a. If it's not already part of your sheet, add the system auto-number column. SAVE. The default name for this column is [Row ID].

1b. Add another column, a text/number column. I will refer to it as [Line#] . You can call it whatever you like and edit my formula accordingly. This is the column that will maintain a 1,2,3 list regardless if rows are deleted, moved or added.

[Line#]

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

The formula below looks intimidating because you are pulling two separate fields from the prior field (Length and Time) then comparing them to your cross sheet. Because essentially three lookups are happening, the formula is lengthy. If you want to split these lookups out into additional helper columns, let me know. The advantage of splitting is if one of the lookups break, you would be able to find it more easily. Breaking formulas into small steps is how I almost always construct my sheets.

Step 2 Insert helper column (text/number) for the formula you desire as a workaround for avoiding a circular reference. I called this column [New Schedule Time]. I used Index/Match or Index/Collects in the formula below. Instead of the table that one creates with for the range of a Vlookup, you will be inserting one column at a time. My range name identifies what column you need to insert as the cross sheet reference. You will need to physically create each of these cross sheet ranges that all refer to what you called your reference sheet.

[New Scheduled Time]

=IF(lvl@row = 0, "09:00", IF(INDEX(Length:Length, MATCH([Line#]@row - 1, [Line#]:[Line#], 0)) = 50, INDEX({Time 50}, MATCH(JOIN(COLLECT(Time:Time, [Line#]:[Line#], @cell = [Line#]@row - 1)), {Time Time}, 0)), IF(INDEX(Length:Length, MATCH([Line#]@row - 1, [Line#]:[Line#], 0)) = 45, INDEX({Time 45}, MATCH(JOIN(COLLECT(Time:Time, [Line#]:[Line#], @cell = [Line#]@row - 1)), {Time Time}, 0)), IF(INDEX(Length:Length, MATCH([Line#]@row - 1, [Line#]:[Line#], 0)) = 30, INDEX({Time 30}, MATCH(JOIN(COLLECT(Time:Time, [Line#]:[Line#], @cell = [Line#]@row - 1)), {Time Time}, 0)), IF(INDEX(Length:Length, MATCH([Line#]@row - 1, [Line#]:[Line#], 0)) = 60, INDEX({Time 60}, MATCH(JOIN(COLLECT(Time:Time, [Line#]:[Line#], @cell = [Line#]@row - 1)), {Time Time}, 0)), INDEX({Time 30}, MATCH(JOIN(COLLECT(Time:Time, [Line#]:[Line#], @cell = [Line#]@row - 1)), {Time Time}, 0)))))))

Will this work for you?

Kelly