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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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...
 
Last edited:

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,150
Office Version
2019
Platform
Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,150
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,150
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,082,106
Messages
5,363,185
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top