Hi - sorry if my wording has been unclear so far, as I am quite novice at excel. This forum I must admit has been fantastic so far. Just to answer the questions that you asked.
1) The sheet housing the data is named ALL
2) I want to be able to include new data should it be updated
3) Column E houses letters. Column B houses date (i.e. numbers). Column J houses numbers. These are three columns I need to look up the second last values of.
I have attached an example file for you - hopefully this helps as to what I am looking for. This is a smaller much condensed version of the file I am dealing with. Thank You
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
adding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {mso-number-format:"Short Date";} --> </style>Below is an example similar to the ALL sheet I am dealing with, hopefully this is of assistance
[...]
Thank You.
The ALL sheet, A:J...
[TABLE="width: 518"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" width="108"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;" width="71"> <col style="width: 48pt;" span="5" width="64"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 108, bgcolor: transparent"]
Date[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 71, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA1[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/1/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]X[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]225[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA2[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/3/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]426[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA3[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]2/5/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]922[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA4[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3/8/2011[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]888[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA5[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]23/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]726[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA6[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]24/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]455[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA7[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]25/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]77[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA8[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]26/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]92[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA9[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]27/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]89[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA10[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]28/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA11[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]29/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]44[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA12[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]30/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA13[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]31/01/12[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]28[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA14[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]1/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]827[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA15[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]2/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]466[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA16[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]924[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA17[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]4/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]899[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA18[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]5/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]226[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA19[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]6/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]887[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]AA20[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]7/2/2012[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl67, width: 71, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]26[/TD]
[/TR]
</tbody>[/TABLE]
Lrow, Scope ALL:
Rich (BB code):
=MATCH(9.99999999999999E+307,ALL!$B:$B)
Brange, Scope Workbook:
Rich (BB code):
=ALL!$B$2:INDEX(ALL!$B:$B,ALL!Lrow)
Drange, Scope Workbook:
Rich (BB code):
=ALL!$D$2:INDEX(ALL!$D:$D,ALL!Lrow)
Erange, Scope Workbook:
Rich (BB code):
=ALL!$E$2:INDEX(ALL!$E:$E,ALL!Lrow)
Jrange, Scope Workbook:
Rich (BB code):
=ALL!$J$2:INDEX(ALL!$J:$J,ALL!Lrow)
The Lookup sheet, A:E...
[TABLE="width: 395"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;" width="129"> <col style="width: 103pt; mso-width-source: userset; mso-width-alt: 4892;" width="138"> <col style="width: 99pt; mso-width-source: userset; mso-width-alt: 4693;" width="132"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 129, bgcolor: transparent"]
Second Last E[/TD]
[TD="class: xl68, width: 138, bgcolor: transparent"]
Second Last B[/TD]
[TD="class: xl68, width: 132, bgcolor: transparent"]
Second Last J[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]
Idx[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]ABC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]29/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]BBC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]30/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]66[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]CNN[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]31/01/12[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]CBC[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]N[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]1/2/2012[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]827[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]FOX[/TD]
[TD="class: xl66, width: 129, bgcolor: transparent"]Y[/TD]
[TD="class: xl67, width: 138, bgcolor: transparent"]2/2/2012[/TD]
[TD="class: xl66, width: 132, bgcolor: transparent, align: right"]466[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
B2, copied down:
C2, copied down:
D2, copied down:
E2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=LARGE(IF(Drange=$A2,ROW(Drange)-ROW(INDEX(Drange,1,1))+1),2)
See:
https://dl.dropbox.com/u/65698317/aaConditionalSecondLastValue%20Public%20ls23%20.xlsx