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.

=IF(ISBLANK(A1),D1=A2,D1=A1)

"IF A1 is blank, THEN D1 equals A2, ELSE D1 equals A1"

Dave
--
Brevity is the soul of wit.


"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.

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.

Thanks for your help but I can't figure out how to tie in the VLOOKUP part of
my current formula with your suggestion and still run through all the shifts
just for the first line:

D1=IF(A1="","",VLOOKUP(A1,$BY$1:$BZ$20,2,0))

I replaced A1="","" with your suggestion, then added in A20, A40, and A60 as
the first line for other shifts (each with it's own VLOOKUP) and I got an
invalid message.

"Dave F" wrote:

=IF(ISBLANK(A1),D1=A2,D1=A1)

"IF A1 is blank, THEN D1 equals A2, ELSE D1 equals A1"

Dave
--
Brevity is the soul of wit.


"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.

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.