How do I change a cell reference every time there is a blank cell?

[Simon Lloyd]

Reference to what? are you using a formula? if so what is it?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Read]

I am trying to copy a cell value in Column A to Column B. I want to stop the
copy at a blank row and start the formula over referencing the new dataset.
Example:
Column A Column B
bread bread
943234 bread
234112 bread

milk milk
232134 milk
123134 milk

Everything I have tried moves bread down the Entire ColumnB

 

[Glenn]

Assuming your data starts in A1, in B1:

=A1

In B2:

=IF(A2="","",IF(A1="",A2,B1))

and copy down.

 

[Read]

Excel says that formula creates a circular reference. Basically I need b1,b2
and b3 to say bread and b5,b6 and b7 to say milk. When I make the a formula
absolute and drag it down column b the entire column =bread. I need a formula
to recognize tht a4 or b4 is blank and reference a5 instead of a1 for the
next data set. Any advice?

 

[Glenn]

Did you put my exact formulas in the exact cells I recommended? If not, let me
know where you put them because they need to be adjusted properly.