Formula to skip blank cells

[FJMD1003]

Hi all,

I'm looking to create a formula that will skip past any blanks until it finds the latest and most up-to-date value.

Some context: I wish to return a latest estimate value to a cell (A5), and this value is updated quarterly. Let's say that the quater 1 value sits in cell A1, Q2 in A2, Q3 in A3 and Q4 in A4. I wish cell A5 to display the latest estimate as soon as a user updates it on a quarterly basis, but default to the previous quarter's estimate if that latest view is unavailable.

I've tried using some IF(ISBLANK...) combinations but am getting nowhere - any ideas greatly appreciated!

NB want to try and avoid Macros across this worksheet so a formula solution would be best.

Many thanks for your help guys,

Fred

 

[Andrew Poulsom]

Welcome to MrExcel.

Try eg:

=LOOKUP(9.99999999999999E+307,A2:A100)

 

[FJMD1003]

Thanks for that tip Andrew though I'm still struggling to make it work in this array e.g.

<TABLE style="WIDTH: 661pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=879><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 374pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl63 height=20 width=497 colSpan=6>Quarterly Revenue results and Fully Year Latest Revenue Estimates (LRE) 2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=82></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=152></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>Q1</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q2</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q3</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65>Q4</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 rowSpan=2 width=152>Full Year Latest Revenue Estimate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 height=20>Q1 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q2 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q3 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Q4 Revenue</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68>Full Year LRE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67 height=20> £ 45.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 200.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 90.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 200.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 145.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70> £ 195.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67> £ 180.00 </TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl69> £ 200.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #7f7f7f; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71>??????</TD></TR></TBODY></TABLE>

I would like it to return a latest estimate value in the cell with question marks in it which automatically updates when the next quarter's data is input as the year progresses.

Thanks for your support with this!

 

[Andrew Poulsom]

Try:

=LOOKUP(2,1/((A2:H2=B2)*(A3:H3)),A3:H3)

 

[FJMD1003]

Thanks Andrew that worked brilliantly!

Much appreciated,

Fred

 

[shuskey06]

Try:

=LOOKUP(2,1/((A2:H2=B2)*(A3:H3)),A3:H3)

I'm looking to the same thing but in a spreadsheet calendar.