Search for first cell with data

[Jeff]

I want to place a formula in a cell to find the first cell
it can with data.

For example, I have each day of a month listed in rows A1
to A31 as 7/1/2003...7/31/2003. On weekdays data will
populate in column B but weekends and holdays cells in B
will be empty. In column C I want to place the value in
cell B from the line above. So if I am in cell C5 it
should read -(B4) but if A5 were a holiday or weekend B5
would be blank and therefore cell C5 should be blank.

I would like a formula to automatically look for the first
cell in column B5 ABOVE it that has data and place it in.
SO for example 7/7/03 is on row 7 needs to pull data from
B3 and place in C7, I have tried statements with if and
find but can not sem to figure out what would work.

If anyone knows I would appreciate it.

 

[Steve Smallman]

Jeff,
there are a couple of options. The simplest to describe here is using nested
IF functions.

using your example, in C5 enter =IF(B4<>"", B4,IF(B3<>"", B3, B2))

for the sake of clarity I have only used two IF functions.

If works just like the basic IF..THEN...ELSE statement. It is constructed
around a logical test that evaluates to either true or false, and then
provides a result based on that evaluation.

In the example above;
=IF( defines the function and commences the parameters (tells excel its
an IF)
B4<>"" looks in cell B4 and tests to see if the contents of cell B4 are
a zero length or empty string, this test evaluates to TRUE if it is not a
zero length string <>=does not equal
, separates the logical test
B4 is the value if TRUE
, separates the value if true from the value if false
IF(B3....... is the value if FALSE, and if the first test returns false,
will commence a new IF Function

Point to note is the two )) at the end, as with any formula, the more
brackets you open, the more you need to close.

I'm not sure about current versions of Excel, but there used to be a limit
of 7 levels of nested If Functions and 255 characters (which I think is now
1024) in a formula. You can do a fair bit with that, but if you need more
then we need to start talking about User Defined Functions, and thats
another response.

Hope this helps

Steve