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!