• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum previous non blank cell in a column

J

Josedv

New Member
Hi there,


In a column where some cells are filled with numbers and others are blank, I need to find a formula for each cell to find the first non blank cell above it and sum +1 to the value of the cell. I've been trying with formulas like ISBLANK and IF but with no success. Any help will be much appreciated.


Best,

J
 
J,


You can select the range starting at the first blank cell(say D2) and ending at the last cell with number( say D10)


Then use CTRL+G, special, blanks, = D3+1, CTRL+enter.


This should take care if the numbers and blanks are alternating.


Thanks
 
Hi josedv,


Just trying to elaborate niting's post..


visit below URL..

http://www.contextures.com/xlDataEntry02.html


In create formula section, instead of A2, write =A2+1.


You may adapt the VBA also from the same.. if you face any problem..

Please contact "Debra" or "Debraj"... :)


Regards,

Deb
 
Hi Josedv,

Not sure if you are looking to fill all the blank cells or just obtain the next number in sequence leaving the existing blank cells as is.


If you are looking for the next number in sequence, you could try something like the following:

=LOOKUP(99999,N(A$1:INDEX(A:A,ROW()-1)))+1


(Example shown is for a cell in column A. Copy it to additional columns as needed.)


Cheers,

Sajan.
 
Hi all,


Thanks for the help. Unfortunately Niting and Debraj's seems not to work for some reason.


Sajan, your formula is close to work but the only problem is that after the blank cell starts the sequence again and what I want is to continue the sequence from the previous non blank cell + 1. For example:


Columm A

1

2

3

Blank

4

5

Blank

Blank

Blank

6

7

...


The blank cells change all the time depending on the data I select from the data base.


Hope I managed to make it a bit more clear now.


Thanks again!

Josedv
 
Hi, Josedv!


Have you tried the procedures FillColBlanks, FillColBlanks_Offset and FillColBlanksSpecial, or downloaded the sample file at http://www.contextures.com/ExcelTemplates/FillBlanksRpt.zip, as stated in Debraj Roy's post? If so and still having problems, would you please upload your sample file with the procedures you used or tested?


And regarding your comment about Sajan's post, which values do you want to assign to the 4 blanks cells (one after 3 and the other three after 5) of your last example? And should all the other values remain unchanged?


Regards!
 
Hi SirJB7,


Thanks for the info. The thing is that I want the blank cells to remain empty as the rows wth blanks will be hidden later on, hence "fillcell" sort of formulas is not an option.


All the other values will change depending on the previous non blank cell. They should always show the value of the previous non blank cell +1. It doesn't matter if the previous non blank cell is right above or 6 rows above.


Hope this helps!

Josedv
 
Hi, Josedv!


Let me see if I understood correctly the whole thing:

a) you have a column with number or empty cells, no formulas

b) you want to keep the column as you wrote upwards, 1-2-3-blank-4-5-blank-blank-blank-6-7 no matter what and how cells in that column got updated

c) how might they be updated?

d) what do you have to do with previous values, before re-numbering?

e) is this a only once process or it'd be repeated?

f) do you mind if helper columns are used?


Regards!
 
Hi SirJB7,


Ok let me try and answer your questions:


A) yes

B) what I wrote was just an example, the blank cells (and the number of blank cells in the columm) depends of the value I choose on a pivot table

C) they are updated by changing the selection on the pivot table

D) the numbers are used to create target values on a graph (similar to a bullet chart). If the numbers change, the graph will change.

E) it will be repeated every time I change the selection on the pivot table.

F) helper columms can be used, but bear in mind that some of the rows will be hidden, so helper columns will have to be placed in another area of the sheet.


Please let me know if you need any further info.


Thanks again!

Josedv
 
Hi ,


I am not sure I have understood your requirement , but try this formula in A2 , and copy it downwards ; A1 is to be blank.


=MAX(OFFSET(A1,,,-ROWS($A$1:A2)+1))+1


I have assumed that the numbers will be in ascending order , and not randomly ordered.


Narayan
 
You must log in or register to reply here.
Back
Top