Formula to skip blank cells

FJMD1003

New Member
Joined
Mar 9, 2011
Messages
16
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

FJMD1003

New Member
Joined
Mar 9, 2011
Messages
16
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,618
Messages
5,838,390
Members
430,545
Latest member
c3310

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top