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

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

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!