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

Options
✭✭✭

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...

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!