Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rcmodelr
 
Posts: n/a
Default Lookup with search range start based on position of last blank lin

Here is some sample data...

A B C D E
Arr. Trk
Lot time Grower No. Driver
r2 Mary
1:00 Odom
2:00 Jesse
2:00 3:00 Odom
2:00 4:00 Jesse
a1 Total

a2 Lingo
2:00 5:00 Odom
2:00 6:00 Jesse
2:00 7:00 Odom
2:00 8:00 Jesse
a2 Total

Column A contains Lot number and calculated turnaround time for each driver.

However, with second farm drivers come from, first load should NOT have a
calculated turnaround time.

Is there some way I can set the start of the lookup range to be the cell
address of the last blank (null) entry in the E column so the formula will
NOT look into the data from the previous farm when calculating the individual
driver turn-around time?

Here is formula used in A column currently to calculate turn-around time
with arrival times entered in 12 hour format (NO AM/PM). Formula below
copied from A column first load data line from second farm, Row 17. Same
formula copied into A column for EVERY row that will contain driver and load
data.


=IF(OR(E17="",ISNA(LOOKUP(2,1/(E8:E16=E17),B8:B16))),"",IF(B17LOOKUP(2,1/(E8:E16=E17),B8:B16),B17-LOOKUP(2,1/(E8:E16=E17),B8:B16),(B17+TIME(12,0,0))-LOOKUP(2,1/(E8:E16=E17),B8:B16)))

First IF handles error trapping so A remains blank if no data, or no
matching data found in E column. Second IF handles error trapping to adjust
if current arrival time value is less than previous arrival time value for
this driver.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto x range start value, xy charts [email protected] Charts and Charting in Excel 5 January 24th 05 11:49 PM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 06:29 PM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 03:03 PM
Inserting Blank Rows Macro? Michael Saffer Excel Worksheet Functions 2 November 9th 04 06:23 PM
Use MATCH to find position of max in 2D range? Peter B Excel Worksheet Functions 4 October 28th 04 05:23 PM


All times are GMT +1. The time now is 08:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"