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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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
Column A	Column B

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

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

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

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

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

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

</tbody>

Sheet2​

[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=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(A2="",N(B1),A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.