Repeat cell reference until blank cell is reached then skip one cell down and use new cell reference. Using formula not VBL

[Johnny Thunder]

Repeat cell reference until blank cell is reached then skip one cell down and use new cell reference. Using formula not VBA

Hello,

I have a list that looks like this.

Column A Column B
(Formula) (Names)
a2_______ John
a3 _______002
a4 _______003
a5 _______065
a6 _______4123
a7 _______Ac7
a8
a9_______ Stacie

In Cell A2 I need a formula that will say copy b2 (John) down until blank cell is reached (B8) skip that cell and use the next cell as your copy reference B9 (Stacie) would be the result and so on down the list...

 

[jasonb75]

Re: Repeat cell reference until blank cell is reached then skip one cell down and use new cell reference. Using formula not VBA

Not possible, try this macro for the task.

http://www.mrexcel.com/forum/showpost.php?p=2274959&postcount=8

As you have text entries you will need to change Currency to String in the 13th line of code.

 

[Johnny Thunder]

The reason I ask is because I have seen it done before. I don't have the spreadsheet were the formula was listed but it worked. it was an if(then statement.

Also, the VBA code that is written in the attachment is very confusing to read through, can you translate it for me? or re-write it so that it pertains to only the column that has names. I don't need the code that converts text to currency. I just need the code that will copy the name column.

Thank you!

 

[jasonb75]

Ok, so with a bit of searching I'm about to prove me wrong.

There's a sample of what you're after here
http://answers.yahoo.com/question/index?qid=20080117231012AAM9YNf

Changing the formula there to one that should make some sense to you

=IF(ROW()-ROW(A2:A9)+1>ROWS(B2:B9)-COUNTBLANK(B2:B9),"",INDIRECT( ADDRESS(SMALL((IF(B2:B9<>"",ROW(B2:B9),ROW()+ROWS(B2:B9))),ROW()-ROW(A2:A9)+1),COLUMN(B2:B9),4)))

copy the formula, paste into A2 then hold ctrl + shift and press enter to enlcose it in {}

use autofill to copy the formula down in A3 onwards.

Note it only covers your sample of A2:A9, if your actual data table is bigger you need to change the formula to cover it!

 

[AlphaFrog]

What if you put this in A2 just to get things started...
=B2

Then in A3 put this...
=IF(B3="","",IF(A2="",B3,A2))
...and copy it down column A

 

[jasonb75]

What if you put this in A2 just to get things started...
=B2

Then in A3 put this...
=IF(B3="","",IF(A2="",B3,A2))
...and copy it down column A

Essentially that formula results in =B$2 filled down.

 

[Johnny Thunder]

That was a huge formula that returns exactly what was in the first cell only and skips blank cells.

this small formula does the exact same thing but much smaller. =IF(B1="","",B1)

What I wanted the formula to do was keep returning B1 until the first occurence of blank, move one cell down and then begin returning whats in the cell directly under the blank. I achieved this with a simple formula but it was not consistent.

=IF((OR(B6="",B5,B6)),IF(B6="",A5,B6)) and this works perfectly for what I am trying to do exept for when it see's the blank cell it references the cell directly above the formula which is "John" and then when I run my pivot I get John counted with no totals. So I just filter by totals and delete all blanks and it will remove the one "John" that should have returned a blank.

Tell me what you think.

 

[jasonb75]

If it works, it's probably the best you'll get, what you actually want, if it can be done, would probably be an even longer formula that the last one I found.

 

[T. Valko]

Tell me what you think.

AlphaFrog's suggestion does what you want.

 

[AlphaFrog]

Thank you Mr. #NAME?. I just assumed I didn't get the question.