• 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.

Alternate auto fill in excel

X

xljgd

Member
Hello All,


Can anyone help me with the formula to automatically autofill blanks in excel but in adjacent alternate rows.


Eg

Col A col B


Name Dicipline

Name1 Finance

blank line

Quality

blank line

Security

Blank line


Name 2 Security

blank line

Quality

Blank line


The intent is that all blank lines must be filled by data directly above it

eg.


Name 2 Security

Security

Quality

Quality


and also Name 2 should be listed against each line item.


Name 2 Security

Name 2 Security

Name 2 Quality

Name 2 Quality


I tried chandoo example of Ctrl A selecting entire table Goto special blank and than ctrl Enter it populated the first column but did not do same thing for column B


Appreciate your time taken to read this post.

thanks for all your help.
 
Xljgd


Firstly, Welcome to the Chandoo.org forums


On the main Forum page there are 3 Green Sticky posts at the top of the page

The 3 posts welcome you and setout some rules for the forums


The Chandoo.org web site and Forums have a Google Search built in (Top right corner of this screen) and so you should use that as a first point to look for solutions


Using the 4 terms "Fill from cell above" returns a link to:

http://chandoo.org/forums/topic/fill-in-blank-cell-from-cell-above


I Think Lukes solution will help you.
 
Thanks a ton for your prompt response. I have tried Luke response before to autofill Column A and it was successful. But for some reason it was not detecting blanks in Column B. PSG response of =IF(B2="",B1,B2) in my case column B solves my problem. Thanks a ton for this logic.It will save me a lot of ctrl c + ctrl v steps.
 
Does column B contain cells that literally have "" in them? If so, Special Cells - Blanks won't work, but the formula would.
 
is it possible in excel that some cells look like blank but does not return blank.

for eg.=IF(B2="",B1,B2)


when i use if (b2="",true,false)


it returns false telling me that b2 is not blank and yet there is no value in it. so when i do this step and do the paste special only value it does not work as it is supposed to. i tried the same formula in a new worksheet and it worked like a charm. currently i am manually updating my rows. is there any explanation. as you pointed out special cells-blanks does not grey out the blank cells.
 
it is possible to have cell containing empty space but looks like blank cell.


you can try your formula like this. In B2, hit the F2 key. See where the cursor is. if it doesn't look like resting on the far left of the cell, that means someone has hit the space bar once more multiple times in that cell. so when you use you formula it will return the value of B2 because B1 is not truly empty.
 
You must log in or register to reply here.
Back
Top