How to create a formula that only affects part of the column...

For the days that are marked DBL in the Time column, we are trying to adjust the formula in the Arrival Time column so that only the first 4 rows are 1.5 hours before the time listed in the Time column and the rest of that day is 1 hour before the listed time...i.e.. 08:15 = 06:45 (1.5 hours) and 08:30 = 07:30 (1 hour). The current formula used in the Arrival Time column is...

"=IF(lvl@row = 0, " ", VLOOKUP(Time@row, {Time Range 1}, 5, false))"...

Below are the screen shots for the Surgery Scheduling sheet we are using as well as the reference sheet with the times to post in the Arrival Time column...

Thank you in advanced...

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Hi @JJLewis,

    It seems we need to index rows in your sheet to determine RowID, then setup formulas depend on their RowID.

    First, create RowID column with a formula :

    =COUNTIF(Date$1:Date@row, Date@row) - 1

    If RowID<=4 : use 1.5 hours before the time listed in the Time column, else 1 hour

    Then modify your formula in the Arrival Time column as below:

    =IF(lvl@row = 0, " ", IF(RowID<=4, VLOOKUP(Time@row, {Time Range 1}, 5, false), VLOOKUP(Time@row, {Time Range 1}, 6, false)))

    Hope that helps.


    Gia Thinh Technology - Smartsheet Solution Partner.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Hi @JJLewis,

    It seems we need to index rows in your sheet to determine RowID, then setup formulas depend on their RowID.

    First, create RowID column with a formula :

    =COUNTIF(Date$1:Date@row, Date@row) - 1

    If RowID<=4 : use 1.5 hours before the time listed in the Time column, else 1 hour

    Then modify your formula in the Arrival Time column as below:

    =IF(lvl@row = 0, " ", IF(RowID<=4, VLOOKUP(Time@row, {Time Range 1}, 5, false), VLOOKUP(Time@row, {Time Range 1}, 6, false)))

    Hope that helps.


    Gia Thinh Technology - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!