Show value until now value occurs

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hello all,

I have a table with the following values:
1613905533741.png

Now I want to fill the next cell always with the previous value until a new value occurs.
I think I have to add an additional column.
E.g. For call C3 if A3 is empty respectively #NV than put the value of A2 in here ect. until in C7 the new value from A7 has to be put.

I think that is not that difficult but I am unfortunately not able to do that. :)

Thank you in advance.

Benjamin
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,871
Office Version
  1. 365
Platform
  1. Windows
I'm guessing that there must be formulas in those cells, so you just need to edit the existing formula to trap the error.
The formula in B2 would be something like
Excel Formula:
=WENNNV(existing formula;B1)
 

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hello Jason,

Yes but I am not able to fix the value of B1 because these value only has to be used for the next cells than and afterwards a new value occurs which should be the value then.
Any ideas how to implement something like "use value from B1 for C1until there is another value in B than use this value and so on"?

Benjamin
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,871
Office Version
  1. 365
Platform
  1. Windows
The reference is relative, so as it fills down in the table B1 changes to B2, B3, etc. Always looking at the row above the formula.

It works perfectly well for what you have described, as you will see from the example below. This is a method that I use frequently.
I've used a simple calculation in place of your existing formula as we don't know what that is.

Cell Formulas
RangeFormula
A3:A8,A10:A15A3=NA()
B2:B15B2=IFNA(ABS(A2)/2,B1)
 
Last edited:

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top