Indexing formulas

[Barrakooda]

Hi all

I am trying to return a value in a cell on sheet 1 from the last value in a row on sheet 2. However this value contains both text & numerical values (work orders) e.g. TE22956.

I tried an Index formula (one that required at least 1 text value & 1 numerical value) bit it didn't seem to work. I would try to link but don't have it on my iPhone.

Any ideas on what I should try?

Thanks

 

[Robert Mika]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	*	*	*	*	*	*	*	*	*	*	*	TE22956
Try this if the patter is the same Sheet2

Excel Workbook
	A	B
1	*	*
2	*	*
3	*	*
4	*	22956
5	*	*
6	*	*
Sheet1

 

[Barrakooda]

Thx Rob, appreciated.

Will that formula select the max value in the row or the last value?

May I ask what the "<>" & ",3 6" do?

Cheers

 

[Robert Mika]

This will return the last match in row less the letters.
The 3.6 is a part of the MID() function=MID("TE22956",3,6)

I assumed that you need the numberical only.
If not please correct/give an example.

 

[Barrakooda]

My apologies

I require it to return the whole value "TE22956" including the letters if possible

 

[Robert Mika]

=INDEX(Sheet2!1:1,MAX((Sheet2!1:1<>"")*(COLUMN(Sheet2!1:1))))
Confirm CTRL+SHIFT+ENTER

 

[T. Valko]

Hi all

I am trying to return a value in a cell on sheet 1 from the last value in a row on sheet 2. However this value contains both text & numerical values (work orders) e.g. TE22956.

I tried an Index formula (one that required at least 1 text value & 1 numerical value) bit it didn't seem to work. I would try to link but don't have it on my iPhone.

Any ideas on what I should try?

Thanks

One way...

Book1
	A	B	C	D	E	F	G	H
1	AC12345	GHT10K	_	TY125P	ZEBRA99	_	TE22956	_
Sheet2

=LOOKUP("zzzzz",Sheet2!1:1)

 

[Barrakooda]

Cheers Valko

Whats the difference between Index & VLOOKUP?

Will give it a try tomorrow, time for bed

Cheers to yo both for your help

 

[T. Valko]

Cheers Valko

Whats the difference between Index & VLOOKUP?

Will give it a try tomorrow, time for bed

Cheers to yo both for your help

You're welcome!

Both are "look up" functions but INDEX is more versatile in that you can do 2 dimensional lookups while the V/H/LOOKUP functions can only do 1 dimensional lookup ups.

 

[Barrakooda]

Would these formulas have any issues if any of the cells in the row are blank? There are times when information is not available.

Valko's worked for TE22956 number but not for cells with numbers only, however there are a few blank cells anlong the way where information couldnt be put due to no information being available.

Sheet 1 (yes i know TExxxxxx numbers are not there they are off to the right towards end of row, just wanted to show blank cells)

<TABLE style="WIDTH: 314pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=417><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" span=3 width=73><TBODY><TR style="HEIGHT: 29.25pt; mso-height-source: userset" height=39><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffffcc; WIDTH: 149pt; HEIGHT: 29.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=39 width=198></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=73>DT01</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=73>DT02</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=73>DT03</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Last Service Type</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Last Service Hours</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>55575</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>55114</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>55909</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Last Service Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>31-May-12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>28-May-12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>29-May-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffffcc; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Last Service Job Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>TE22545</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>TE22118</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>TE22109</TD></TR></TBODY></TABLE>

Sheet 2 (DT01)

<TABLE style="WIDTH: 1023pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1364><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" span=4 width=76><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" span=3 width=76><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" span=3 width=76><TBODY><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 146pt; HEIGHT: 23.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=31 width=194>SERVICE TYPE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>D</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=68>B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=67>D</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=71>B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=76>MID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>DOWN SMU</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>39942</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>40189</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>40443</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>40712</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>40964</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>41235</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>41968</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>42283</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>42500</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>42750</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>42990</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>43245</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>43529</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>43760</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>DOWN DATE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>16-Dec-09</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>30-Dec-09</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>13-Jan-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>27-Jan-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>10-Feb-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>25-Feb-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>04-Apr-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>22-Apr-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>03-May-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>16-May-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>27-May-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>09-Jun-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>24-Jun-10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>09-Jul-10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>JOB NUMBER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>BODY RAISE CYCLE TIME - secs</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66>19</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66>21</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #c6efce; COLOR: #006100; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66>18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>N/A</TD></TR></TBODY></TABLE>

Using th following formulas

Row 1 =INDEX('DT01'!$B1:$AEO1,MATCH(REPT("z",255),'DT01'!$B1:$AEO1))
Row 2 =MAX('DT01'!B2:AEO2)
Row 3 =MAX('DT01'!B3:AEO3)
Row 4 =LOOKUP("zzzzz",'DT01'!4:4)

Cheers