IF statements and columns

[AndrePrevin]

Hi all, really need your help

I have a series of values and blanks randomly distributed in one column and I want to set up a formula in the next column which looks at the first column and says, if there is a value in first column then take that value, otherwise if there is a blank then use the last cell in which a number occurred.

But where it gets a bit complicated is - I want the formula to update the value which it uses from the first column every time a new number occurs in that column, meaning that as the first column encounters a new value after a series of blanks, the formula will discard the old or previous value and take the new one

To give an idea of what I mean, column B's formula must display the last value from column A even when it encounters blanks in column A until it encounters a new value in column A at which point it must update its output to that new encountered value from column A

Column A	Column B
4	4
	4
	4
2	2
	2
	2

<tbody>
</tbody>

Not sure if this is even possible but I will be really grateful if some kind person could help me with this, please

Thank you
Kind regards

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B
1	Column A	Column B
2	4	4
3		4
4		4
5	2	2
6		2
7		2
Sheet2

Cell Formulas
Range	Formula
B2	=IF(A2="",N(B1),A2)

 

[AndrePrevin]

Hi & welcome to MrExcel.
How about

Excel 2013/2016

	A	B
1	Column A	Column B
2	4	4
3		4
4		4
5	2	2
6		2
7		2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =IF(A2="",N(B1),A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks, I tried that but does not seem to be working for me

 

[fhqwgads]

does not seem to be working for me

do your columns have headers or does data start in row 1? i think Fluff's formula needs you to have a header row.
otherwise use the formula starting in row 2 and just enter the first value manually

 

[AndrePrevin]

Hi fhqwgads,

I tried what you've suggested but that also does not work

 

[Fluff]

Can you please give an accurate sample of your data & explain why the suggestions made "do not work"

 

[AndrePrevin]

Hi Fluff, my apologies I just realised the mistake was mine. Your formula works perfectly.
Thank you so much

If its not too much trouble, can I request your help further?
I have some more functions I need to set up in my spreadsheet

I will attach a sample workbook to make things clearer

Kind regards

 

[Fluff]

This sounds like a different question, in which case you will need to start a new thread.