Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any time a
value is entered into C1, then I want the function to copy the next value it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so of
those entries corresponding numbers in column A will be greater than 0. I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C cells is
not an option.

Does anyone have any ideas?

Put this in C1 and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dim" wrote in message
...
Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each
cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any
time a
value is entered into C1, then I want the function to copy the next value
it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so of
those entries corresponding numbers in column A will be greater than 0. I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C cells
is
not an option.

Does anyone have any ideas?

Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I need
it to keep moving down the A column, and then the next time it finds a value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

"Bob Phillips" wrote:

Put this in C1 and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dim" wrote in message
...
Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each
cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any
time a
value is entered into C1, then I want the function to copy the next value
it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so of
those entries corresponding numbers in column A will be greater than 0. I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C cells
is
not an option.

Does anyone have any ideas?

Sub findgreaterthan()
J = 1
For i = 1 To 100
If IsNumeric(Cells(i, 1)) And Cells(i, 1) 0 Then
Cells(J, 3) = Cells(i, 2)
J = J + 1
End If
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I
need
it to keep moving down the A column, and then the next time it finds a
value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of
it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

"Bob Phillips" wrote:

Put this in C1 and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"dim" wrote in message
...
Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each
cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it
finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any
time a
value is entered into C1, then I want the function to copy the next
value
it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so
of
those entries corresponding numbers in column A will be greater than 0.
I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C
cells
is
not an option.

Does anyone have any ideas?

Believe Bob's array works, but you probably didn't array-enter it correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies, then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col A,
say down to D100? Hide away col D. Col C will return the required results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I need
it to keep moving down the A column, and then the next time it finds a value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result! :-)

I read your two posts, and when Max said that Bob's array works, I went back
and had a look at it. I had it working fine (I thought) but it just wasn't
doing what I wanted. Anyway, I realised what the problem was....I entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops!

So I have a way to achieve what I wanted, but now Im going to try out your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies, then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col A,
say down to D100? Hide away col D. Col C will return the required results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I need
it to keep moving down the A column, and then the next time it finds a value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result!
:-)

I read your two posts, and when Max said that Bob's array works, I went
back
and had a look at it. I had it working fine (I thought) but it just wasn't
doing what I wanted. Anyway, I realised what the problem was....I entered
his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE)
to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies, then
it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col
A,
say down to D100? Hide away col D. Col C will return the required results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I
need
it to keep moving down the A column, and then the next time it finds a
value
greater than zero, to show that corresponding text in cell C2, then
keep
checking down and show the next one in cell C3 and so on. So at the end
of it
checking hundreds of values in column A, I might have seven or eight
(Or
three, or fifty etc) cells at the top of column C filled in with text
from
column B.

I hope that makes sense.....any ideas?

But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Don Guillett" wrote in message
...

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result!
:-)

I read your two posts, and when Max said that Bob's array works, I went
back
and had a look at it. I had it working fine (I thought) but it just
wasn't
doing what I wanted. Anyway, I realised what the problem was....I entered
his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out
your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER
(CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies,
then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using
non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col
A,
say down to D100? Hide away col D. Col C will return the required
results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero,
I need
it to keep moving down the A column, and then the next time it finds a
value
greater than zero, to show that corresponding text in cell C2, then
keep
checking down and show the next one in cell C3 and so on. So at the
end of it
checking hundreds of values in column A, I might have seven or eight
(Or
three, or fifty etc) cells at the top of column C filled in with text
from
column B.

I hope that makes sense.....any ideas?

could

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Phillips" wrote in message
...
But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Don Guillett" wrote in message
...

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result!
:-)

I read your two posts, and when Max said that Bob's array works, I went
back
and had a look at it. I had it working fine (I thought) but it just
wasn't
doing what I wanted. Anyway, I realised what the problem was....I
entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out
your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER
(CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies,
then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using
non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col
A,
say down to D100? Hide away col D. Col C will return the required
results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero,
I need
it to keep moving down the A column, and then the next time it finds
a value
greater than zero, to show that corresponding text in cell C2, then
keep
checking down and show the next one in cell C3 and so on. So at the
end of it
checking hundreds of values in column A, I might have seven or eight
(Or
three, or fifty etc) cells at the top of column C filled in with text
from
column B.

I hope that makes sense.....any ideas?

Hi again,

I was using Bob's array formula fine in my test sheet, but now I've opened a
new file and copied it acorss. I changed some of the values to referance the
correct cells, and pressed ctrl-shift-enter to activate it, but its not
working!!! :-(

This is what I have in the cell and dragged down to the cells below:

{=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))}

Whats wrong?

"Don Guillett" wrote:

could

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Phillips" wrote in message
...
But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Don Guillett" wrote in message
...

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result!
:-)

I read your two posts, and when Max said that Bob's array works, I went
back
and had a look at it. I had it working fine (I thought) but it just
wasn't
doing what I wanted. Anyway, I realised what the problem was....I
entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out
your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER
(CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies,
then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using
non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in col
A,
say down to D100? Hide away col D. Col C will return the required
results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero,
I need
it to keep moving down the A column, and then the next time it finds
a value
greater than zero, to show that corresponding text in cell C2, then
keep
checking down and show the next one in cell C3 and so on. So at the
end of it
checking hundreds of values in column A, I might have seven or eight
(Or
three, or fifty etc) cells at the top of column C filled in with text
from
column B.

I hope that makes sense.....any ideas?

dim,

my formula was specific to the data being in A1 etc. To be more versatile,
use

=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15)) ,ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1),"",INDEX($B$6:$B$15,SMALL( IF($A$6:$A$150,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dim" wrote in message
...
Hi again,

I was using Bob's array formula fine in my test sheet, but now I've opened
a
new file and copied it acorss. I changed some of the values to referance
the
correct cells, and pressed ctrl-shift-enter to activate it, but its not
working!!! :-(

This is what I have in the cell and dragged down to the cells below:

{=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))}

Whats wrong?

"Don Guillett" wrote:

could

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Phillips" wrote in message
...
But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Don Guillett" wrote in message
...

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same
result!
:-)

I read your two posts, and when Max said that Bob's array works, I
went
back
and had a look at it. I had it working fine (I thought) but it just
wasn't
doing what I wanted. Anyway, I realised what the problem was....I
entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out
your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER
(CSE) to
confirm the formula. You should see curly braces { } wrapped around
the
formula by Excel (in the formula bar). If you don't see the curlies,
then it
hasn't been array-entered properly. You gotta try the CSE again.
Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using
non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in
col
A,
say down to D100? Hide away col D. Col C will return the required
results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I
want.

Your formula takes the first value greater than zero that it
finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than
zero,
I need
it to keep moving down the A column, and then the next time it
finds
a value
greater than zero, to show that corresponding text in cell C2,
then
keep
checking down and show the next one in cell C3 and so on. So at
the
end of it
checking hundreds of values in column A, I might have seven or
eight
(Or
three, or fifty etc) cells at the top of column C filled in with
text
from
column B.

I hope that makes sense.....any ideas?

GREAT! :-D Thanks a million Bob, that's working perfect now.

"Bob Phillips" wrote:

dim,

my formula was specific to the data being in A1 etc. To be more versatile,
use

=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15)) ,ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1),"",INDEX($B$6:$B$15,SMALL( IF($A$6:$A$150,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dim" wrote in message
...
Hi again,

I was using Bob's array formula fine in my test sheet, but now I've opened
a
new file and copied it acorss. I changed some of the values to referance
the
correct cells, and pressed ctrl-shift-enter to activate it, but its not
working!!! :-(

This is what I have in the cell and dragged down to the cells below:

{=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))}

Whats wrong?

"Don Guillett" wrote:

could

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Phillips" wrote in message
...
But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Don Guillett" wrote in message
...

The macro approach is much less overhead for your file
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dim" wrote in message
...
Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same
result!
:-)

I read your two posts, and when Max said that Bob's array works, I
went
back
and had a look at it. I had it working fine (I thought) but it just
wasn't
doing what I wanted. Anyway, I realised what the problem was....I
entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc.
Oops!

So I have a way to achieve what I wanted, but now Im going to try out
your
two ways to see how they work aswell. Thanks again.

"Max" wrote:

Believe Bob's array works, but you probably didn't array-enter it
correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER
(CSE) to
confirm the formula. You should see curly braces { } wrapped around
the
formula by Excel (in the formula bar). If you don't see the curlies,
then it
hasn't been array-entered properly. You gotta try the CSE again.
Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using
non-array
formulas
In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) ))
In D1: =IF(A1="","",IF(A10,ROW(),""))
Select C1:D1, copy down to cover the max expected extent of data in
col
A,
say down to D100? Hide away col D. Col C will return the required
results
from col B, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dim" wrote:
Thanks Bob but I've tried that and it doesn't exactly do what I
want.

Your formula takes the first value greater than zero that it
finds,
shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than
zero,
I need
it to keep moving down the A column, and then the next time it
finds
a value
greater than zero, to show that corresponding text in cell C2,
then
keep
checking down and show the next one in cell C3 and so on. So at
the
end of it
checking hundreds of values in column A, I might have seven or
eight
(Or
three, or fifty etc) cells at the top of column C filled in with
text
from
column B.

I hope that makes sense.....any ideas?