Thanks for your help but it won't let me enter an array with merged cells.
When I unmerged the cells, it still gave me a n/a error.

"mama no teeth" wrote:

try this

D1 =INDEX(A1:A10,MATCH(TRUE,A1:A10<"",0))

adjust your range to suit
ctrlshiftenter (not just enter)

"TammyS" wrote:

We have four shifts that rotate between days, swing, and graveyard. There is
always one shift off. We also make a variety of products and lengths (each
day the production schedule changes). Right now, I manually fill out a punch
sheet representing the day's production and I am trying to put the punch
sheet into Excel and have the information transfer from three worksheets.
The problem I have is that I allotted 15 lines per shift just in case there
are many change overs, but sometimes only a couple of lines are used and I
have to hide all the blank lines. Can Excel skip over the blank lines and
find the next line with information?

For instance:
D1=A1 but if A1 is blank, D1=A2 and so on.

Thanks.