Title: Jump to the next non-empty cell and use lates value in the list
Tags: excel
Question: I have the following Excel spreadsheet:
A                      B                    Desired Result Column B
1    Product A                50                     **50**       **50**
2    Product B        =IF(A2="","",B1)                 50           50
3                     =IF(A3="","",B2)
4    Prodcut C        =IF(A4="","",B3)                 50         **40**
5                     =IF(A5="","",B4)
6    =""              =IF(A5="","",B5)
7    Product D        =IF(A5="","",B6)                 50           40
8    Product E        =IF(A5="","",B7)                 50           40
** Input of User
In Column A there is a list of different products. As you can see there can either be empty cells or cells with formula ="".
In Column B I want to achieve that the last value before the first empty cell or ="" cell applies to the other rows.
For example: If I enter a 50 in Cell B1 I want to achieve that this 50 appears next to every product and empty cells or ="" are ignored.
I can achieve this with the following formula:
=IF(A2="","",$B$1)
Now the problem is, that the user can also type a different number in another cell in Column B. For example he could type in a 40 in Cell B4.
In this case I want that the 40 applies to all other following rows instead of the 50 as you can see in the section "Desired Result Column B" in the example above.
How do I have to change my formula in Column B to achieve this?
Comment: Sorry, I tried to explain this with the "Desired Result Column B" section but I understand if it is not completely clear. The user is able to overtype the formula in Column B. The formula should just help him to not having to copy all the values down.

Comment: You say you have a formula in column B, then you say you enter "50" in B1. That means the formula is replaced. I don't understand. Make some examples and it may be easier to understand

Comment: ok.. But it all has to happen in one column? Can't the user input his value in C column?

Here is the accepted answer: Enter the following formula in Cell B2
=IF(A2<&gt;"",INDEX($B$1:$B1,MAX(IF($B$1:$B1<&gt;"",1,0)*ROW($B$1:$B1))),"")
Drag/Copy down as required.
This is an array formula so commit it by pressing Ctrl+Shift+Enter.

Here is another answer: B2: =IF(A2="","",LOOKUP(2,1/LEN($A$1:A1),$B$1:B1))
With the addressing mode used in the LOOKUP formula, it will examine the rows up to the row before the lookup.  If the column A value is not blank, lookup_vector will match the last non-blank cell in column A prior to the row containing the formula, and result_vector will return the value in the same row in column B.
It is rather critical that your user entries are restricted as you have described above.
If the user can make an entry in column B that does NOT correspond to an entry in column A, and you want that entry to be copied down, then use:
=IF(A2="","",LOOKUP(2,1/LEN($B$1:B1),$B$1:B1))
Also, note that the sequence will be upset if the user deletes an entry in column B, instead of replacing it with another number.  The formulas below the deletion will interpret the deleted value as a zero.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:07/16/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel>', 'Title': 'Jump to the next non-empty cell and use lates value in the list', 'ViewCount': 1920, 'id': 45126213, 'user_AboutMe': None, 'user_AccountId': 6357358.0, 'user_DisplayName': 'Michi', 'user_Reputation': 4487.0, 'user_Views': 610.0, 'user_id': 4934150.0}}