I have the following spreadsheet that shows the following and I need to fill
in column B with the reference number in column A and keep doing so if the
value is 0 until it changes to a new reference number and then and then that
cell has to be put in.

A1 B1
ref no ref no of A1
0 ref no of A1
different ref no different ref no
0 as above
0 as above and so on.
Does anyone know how to achieve this automatically. I can do it using the If
statement but these can be massive files and are for inexperienced users.
Thanks in anticipation once again

Suppose you have your data in ColA and Col B;
Row 1 with headers and data starts from row 2

try the below formula in cell B2 and copy down as required...

=IF(A2=0,B1,A2)

Col A Col B
Header 1 Header 2
1 1
0 1
0 1
0 1
2 2
0 2
0 2
0 2
0 2
0 2
3 3
0 3
0 3
0 3


If this post helps click Yes
---------------
Jacob Skaria


"tom_mcd" wrote:

I have the following spreadsheet that shows the following and I need to fill
in column B with the reference number in column A and keep doing so if the
value is 0 until it changes to a new reference number and then and then that
cell has to be put in.

A1 B1
ref no ref no of A1
0 ref no of A1
different ref no different ref no
0 as above
0 as above and so on.
Does anyone know how to achieve this automatically. I can do it using the If
statement but these can be massive files and are for inexperienced users.
Thanks in anticipation once again

Hi
assuming you have header row at A1 and B1
try this in B2 and copy down

=IF(A2<0,A2,B1)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"tom_mcd" wrote:

I have the following spreadsheet that shows the following and I need to fill
in column B with the reference number in column A and keep doing so if the
value is 0 until it changes to a new reference number and then and then that
cell has to be put in.

A1 B1
ref no ref no of A1
0 ref no of A1
different ref no different ref no
0 as above
0 as above and so on.
Does anyone know how to achieve this automatically. I can do it using the If
statement but these can be massive files and are for inexperienced users.
Thanks in anticipation once again

Hi,

In cell B1, use the following formula and copy down

=LOOKUP(2,1/(A$1:A10),A$1:A1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"tom_mcd" wrote in message
...
I have the following spreadsheet that shows the following and I need to
fill
in column B with the reference number in column A and keep doing so if
the
value is 0 until it changes to a new reference number and then and then
that
cell has to be put in.

A1 B1
ref no ref no of A1
0 ref no of A1
different ref no different ref no
0 as above
0 as above and so on.
Does anyone know how to achieve this automatically. I can do it using the
If
statement but these can be massive files and are for inexperienced users.
Thanks in anticipation once again