atomatically fill in cells

[tom_mcd]

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

 

[Jacob Skaria]

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

 

[Francis]

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

 

[Ashish Mathur]

Hi,

In cell B1, use the following formula and copy down

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com