A created a schedule where I input data in cells B4-B28 and B4-Y4. I used a
function to copy the data from the previous cell from the previous column
(ex. C5 is copied from B4, D5 copied from C4, E5 from D4 and so on...)
I want D9 to check the previous rows in the previous column and used the
last data that was used. I want D9 to check to see if C8 has data and if it
doesn't then check C7, then C6, then C5, and C4. Lets just say the C4-C6 has
data, I want D9 to automatically use the data from C6, which is the last cell
with data. Keep in mind that C4-C8 already are using a function. If anybody
has an answer i would really appreciate it. And if you have a question, I'll
be more than gladly to try to explain it better. Thanks.

I am creating a production schedule of houses. So I use a # and text
(ex.671A). I used the fuction =IF(B5="","",(B4)) in cell C5 and continued all
the way through Y5(=IF(X5="","",(X4))). I did this to the next 3 rows also
(C6:Y8).

I only put text in B4 through B6 and B4 through Y4 leaving B7 and B8 empty.
So because of the functions, only row 5 and 6 got filled with data and 7 and
8 were left blank because i used the function to leave a cell blank if the
cell to the left is blank. Then on C9 i used the function
=IF(ISTEXT(B8),(B8),IF(ISTEXT(B7),(B7),IF(ISTEXT(B 6),(B6),IF(ISTEXT(B5),(B5),IF(ISTEXT(B4),(B4),"")) )))
and it gave me the data from B6 which is what I wanted. But before I did
this I had to format the all the cells to be 'text' cells.
The problem that I am having is that when I tried to do the rest of row 9
the same way, it wouldn't work. The cells came out blank. I am wondering if
this is happening because rows 7 and 8 already have function that is giving
these cells the result of 'blank'. and it can only read 'text' cells and
maybe that's why D9-Y9 is not reading these cells. I don't know what other
function to input so that these cells can work.

"Ken Wright" wrote:

You need to tell us what the function returns that woiuld lead you not to
count it as being 'the last data used'.

Various ways to do what you ask, but we have to know what criteria you use
to ignore the results of a function, ie perhaps it returns a blank, or a 0
etc.

Assuming your function returns a blank if 'data not used' then something like

=LOOKUP(99^99,C4:C8)

might do you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Alex s" wrote:

A created a schedule where I input data in cells B4-B28 and B4-Y4. I used a
function to copy the data from the previous cell from the previous column
(ex. C5 is copied from B4, D5 copied from C4, E5 from D4 and so on...)
I want D9 to check the previous rows in the previous column and used the
last data that was used. I want D9 to check to see if C8 has data and if it
doesn't then check C7, then C6, then C5, and C4. Lets just say the C4-C6 has
data, I want D9 to automatically use the data from C6, which is the last cell
with data. Keep in mind that C4-C8 already are using a function. If anybody
has an answer i would really appreciate it. And if you have a question, I'll
be more than gladly to try to explain it better. Thanks.

Hi Alex

I'm not sure I am understanding your requirement, but With 671A, 672A
and 673A entered in cells B4:B6 respectively, I entered a formula in
B7:B9 as
=IF($C$1=1,LEFT(B6,3)+1&"A","") and left C1 blank so they weren't
triggered to produce successive numbers, but contained "".

Then with an array formula in C9 of
{=IF(B8<"",B8,LOOKUP(REPT("z",255),IF($B$4:B8<"" ,$B$4:B8)))}
this returned 673A
Of course, If I had this formula in cells C7 through C9, they would all
return 673A until such time as there is a new value in column B

When I entered a 1 in cell C1, when they would change to
C7 674A, C8 675A and C9 676A with 676A being repeated down the page in
column C as far as the formula had been entered.

Is this the sort of thing you are trying to do?

NOTE
Array formulae are entered and edited using CTRL+SHIFT+ENTER (CSE) not
just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula - do NOT type them yourself.
--
Regards

Roger Govier


"Alex s" wrote in message
...
I am creating a production schedule of houses. So I use a # and text
(ex.671A). I used the fuction =IF(B5="","",(B4)) in cell C5 and
continued all
the way through Y5(=IF(X5="","",(X4))). I did this to the next 3 rows
also
(C6:Y8).

I only put text in B4 through B6 and B4 through Y4 leaving B7 and B8
empty.
So because of the functions, only row 5 and 6 got filled with data
and 7 and
8 were left blank because i used the function to leave a cell blank if
the
cell to the left is blank. Then on C9 i used the function
=IF(ISTEXT(B8),(B8),IF(ISTEXT(B7),(B7),IF(ISTEXT(B 6),(B6),IF(ISTEXT(B5),(B5),IF(ISTEXT(B4),(B4),"")) )))
and it gave me the data from B6 which is what I wanted. But before I
did
this I had to format the all the cells to be 'text' cells.
The problem that I am having is that when I tried to do the rest of
row 9
the same way, it wouldn't work. The cells came out blank. I am
wondering if
this is happening because rows 7 and 8 already have function that is
giving
these cells the result of 'blank'. and it can only read 'text' cells
and
maybe that's why D9-Y9 is not reading these cells. I don't know what
other
function to input so that these cells can work.

"Ken Wright" wrote:

You need to tell us what the function returns that woiuld lead you
not to
count it as being 'the last data used'.

Various ways to do what you ask, but we have to know what criteria
you use
to ignore the results of a function, ie perhaps it returns a blank,
or a 0
etc.

Assuming your function returns a blank if 'data not used' then
something like

=LOOKUP(99^99,C4:C8)

might do you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Alex s" wrote:

A created a schedule where I input data in cells B4-B28 and B4-Y4.
I used a
function to copy the data from the previous cell from the previous
column
(ex. C5 is copied from B4, D5 copied from C4, E5 from D4 and so
on...)
I want D9 to check the previous rows in the previous column and
used the
last data that was used. I want D9 to check to see if C8 has data
and if it
doesn't then check C7, then C6, then C5, and C4. Lets just say the
C4-C6 has
data, I want D9 to automatically use the data from C6, which is the
last cell
with data. Keep in mind that C4-C8 already are using a function. If
anybody
has an answer i would really appreciate it. And if you have a
question, I'll
be more than gladly to try to explain it better. Thanks.

Hi Roger

I'm still having problems with my chart. The problem with the first function
that your gave me is that the numbers vary with A, B, C, and D. See I am
making a production schedule for mobile homes for my company. So the houses
are numbered with A and B if its a double house. And sometimes when a triple
comes out then a C would be required. So instead of 671A, 672A, 673A and so
on it would be 671A, 671B, 672A, 672B and sometimes 672C, and so on. I
noticed on the function you gave, you have "A" and "" but I can't just use an
A and I can't leave it blank because all houses will atleast have an "A" and
"B". So B4:B6 respectively would be 671A, 671B, 672A.

Then I tried using the second function but this still wouldn't work. When I
put in this fonction in C9, it would just show the text or function in the
cell. It's like if it wasn't even putting a function in the cell. It was just
reading it as text.

What I am doing is using row 4 through row 28. In column A, I use Mon-Fri
and I spaced them every 5 rows, so that Mon would have 5 rows, Tues. 5, Wed
have 5, Thurs 5, and Fri 5. Right now I am only inputing houses into 3 rows
because that's the number of houses we are doing a day. But there are some
weeks where we might do 4 houses on Mon or Tues, etc, instead of 3. What I am
having trouble with is that for the first week of Tues, which would be row 9,
I want it to find the last house that was done for Mon. and put it on row 9.
So basically, I would start from C9, because in column B, I myself, input the
houses that we are going to do for the week. So I want C9 to check B8 to see
if it was the last house and if it wasn't, then I want it to check B7, then
B6, B5,B4. So in this example, since I have 3 houses which are B4-B6, C9
would get the house # from B6. And D9 would get C6, E9 gets D6 and so on. So
basically row 9 would get the houses from row 6 but each cell would get the
house # from the previous column in row 6. If in a week we do 4 houses and
want C9 to get the house that was used in B7. And if we do 5, then C9 would
get B8.

I don't know if this makes sense, but i hope you or anybody can help me.
Thanks in advance.

"Roger Govier" wrote:

Hi Alex

I'm not sure I am understanding your requirement, but With 671A, 672A
and 673A entered in cells B4:B6 respectively, I entered a formula in
B7:B9 as
=IF($C$1=1,LEFT(B6,3)+1&"A","") and left C1 blank so they weren't
triggered to produce successive numbers, but contained "".

Then with an array formula in C9 of
{=IF(B8<"",B8,LOOKUP(REPT("z",255),IF($B$4:B8<"" ,$B$4:B8)))}
this returned 673A
Of course, If I had this formula in cells C7 through C9, they would all
return 673A until such time as there is a new value in column B

When I entered a 1 in cell C1, when they would change to
C7 674A, C8 675A and C9 676A with 676A being repeated down the page in
column C as far as the formula had been entered.

Is this the sort of thing you are trying to do?

NOTE
Array formulae are entered and edited using CTRL+SHIFT+ENTER (CSE) not
just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula - do NOT type them yourself.
--
Regards

Roger Govier


"Alex s" wrote in message
...
I am creating a production schedule of houses. So I use a # and text
(ex.671A). I used the fuction =IF(B5="","",(B4)) in cell C5 and
continued all
the way through Y5(=IF(X5="","",(X4))). I did this to the next 3 rows
also
(C6:Y8).

I only put text in B4 through B6 and B4 through Y4 leaving B7 and B8
empty.
So because of the functions, only row 5 and 6 got filled with data
and 7 and
8 were left blank because i used the function to leave a cell blank if
the
cell to the left is blank. Then on C9 i used the function
=IF(ISTEXT(B8),(B8),IF(ISTEXT(B7),(B7),IF(ISTEXT(B 6),(B6),IF(ISTEXT(B5),(B5),IF(ISTEXT(B4),(B4),"")) )))
and it gave me the data from B6 which is what I wanted. But before I
did
this I had to format the all the cells to be 'text' cells.
The problem that I am having is that when I tried to do the rest of
row 9
the same way, it wouldn't work. The cells came out blank. I am
wondering if
this is happening because rows 7 and 8 already have function that is
giving
these cells the result of 'blank'. and it can only read 'text' cells
and
maybe that's why D9-Y9 is not reading these cells. I don't know what
other
function to input so that these cells can work.

"Ken Wright" wrote:

You need to tell us what the function returns that woiuld lead you
not to
count it as being 'the last data used'.

Various ways to do what you ask, but we have to know what criteria
you use
to ignore the results of a function, ie perhaps it returns a blank,
or a 0
etc.

Assuming your function returns a blank if 'data not used' then
something like

=LOOKUP(99^99,C4:C8)

might do you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Alex s" wrote:

A created a schedule where I input data in cells B4-B28 and B4-Y4.
I used a
function to copy the data from the previous cell from the previous
column
(ex. C5 is copied from B4, D5 copied from C4, E5 from D4 and so
on...)
I want D9 to check the previous rows in the previous column and
used the
last data that was used. I want D9 to check to see if C8 has data
and if it
doesn't then check C7, then C6, then C5, and C4. Lets just say the
C4-C6 has
data, I want D9 to automatically use the data from C6, which is the
last cell
with data. Keep in mind that C4-C8 already are using a function. If
anybody
has an answer i would really appreciate it. And if you have a
question, I'll
be more than gladly to try to explain it better. Thanks.