Return first non blank cell from a given point

raymaster98

Board Regular
Joined
Oct 28, 2009
Messages
212
In Excel 2003, I am using the following formula to return a time:

{=OFFSET(INDEX(BW5:BW24,MATCH(TRUE,BW5:BW24<>"",0)),0,-5)}

I need to modify the above formula to look for the first non blank cell above that point. Any help would be most appreciated. Thanks, Kenny
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I'm a tad confused - presumably you're saying the first non-blank above the range returned by the OFFSET ?

I base the above on the fact that the existing function is finding the first non-blank in BW range so by definition there are no non-blanks above that cell (other than perhaps in BW1:BW4)

If so... perhaps you could use:

=LOOKUP(9.99E+307,BR5:INDEX(BR5:BR24,MAX(1,MATCH(TRUE,INDEX(BW5:BW24<>"",0),0)-1)))

The above assume BR5:BR24 contain numerics - based on Time comment
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,329
Messages
5,510,635
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top