Copying down a value in a cell until the Value Changes

[melbwarrior]

Hi

I am struggling to work out a formula for the below scenario:

In Column A1

123


456


789

In Column B1 I am looking to copy the value in A1, filling in the blanks with the same value until a new value is reached in A1.

The Ideal result would be in B1

123
123
123
123
456
456
456
456
789
789
789

I assume this will be a simple formula but I am unable to work it out!

If I haven't been clear please let me know

Thanks

Will

 

[arthurbr]

Starting in B2 =IF(A2<>"",A2,B1) and drag down
B1 should be entered manually

 

[jmacleary]

Excel 2007 32 bit

	B

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B1[/TH]
[TD="align: left"]=A1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B2[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]ISBLANK(A2),B1,A2[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there. Try the above formulas in B1 and B2.

 

[melbwarrior]

Thank you very Much