Reference to next non-blank cell

[Hilvert Scheper]

Hi there Dear specialists,

Can I Please have some help with this;
I want a Formula that finds and returns the Text-string from the next
Non-Blank Cell in the previous column. The reason why I'm asking is that this
is part of a Pivot-Table due to which some cells are empty and the number of
empty cells is Variable.

Many Thanks for Your help, I hope this example helps:

Column A Column B
(blank) This Cell (B1) needs to find the next non-blank cell in column
A,
(blank) and return text-string "SN2 2QH" from Cell A4
(blank)
SN2 2QH

Thank You again,
Hilvert

 

[Roger Govier]

Hi Hilvert

You need to use the GetPivotData function.
For help on this take a look at
http://www.contextures.com/xlPivot06.html

 

[Hilvert Scheper]

Dear Roger,
Thank You for Your advice,
I am Sorry for not making myself understood here, my apologies for the
misunderstanding. My question is part of whet I need to include in a Macro.
The Macro creates the Pivot-Table, then Copies the pivot table and pastes
Values so that I can then work with the data retrieved from a Database, and
The data that I need to calculate is and can not be retrieved in this
pivot-table.
The problem here is that I need to make calculations using data in one row
and comparing it against the next non-blank cell in a column....

Still doesn't make it any easier I presume, sorry, and Many Thanks for Your
attention again!
Hilvert

Hi Hilvert

You need to use the GetPivotData function.
For help on this take a look at
http://www.contextures.com/xlPivot06.html

Many Thanks for Your help, I hope this example helps:

Column A Column B
(blank) In Cell B1 I need to find the next non-blank cell in
(blank) column A, and return text-string "SN2 2QH" from
Cell A4
(blank)
SN2 2QH

Thank You again,
Hilvert

 

[Roger Govier]

Hi Hilvert

Difficult to understand what you mean, without seeing the PT layout and the
macro.
If you want to mail me a copy of the workbook I will take a look
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[Hilvert Scheper]

Hi Roger,
Many Thanks for Your reply,
I have replied to You as You suggested.
Hope You can help me!!
Rgds,
Hilvert

 

[Hilvert Scheper]

Hi All,
having done some investigations myself, I have Not found a solution for my
problem using a Formula, However I have found a way around it using a Macro
instead.
Basically Move a cell to the left, find a non-Blank cell, copy and paste
into the cell where You started.
A Most Warmhearted Thank You to Roger for trying to help!
Hilvert

The Macro Code I used is:

alphabet1 = ActiveCell.Address
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
ActiveCell.Offset(0, -1).Select
Selection.Resize(numRows + 20, numColumns).Select
Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Select
alphabet22 = ActiveCell.Address
Selection.Copy
Range(alphabet1).Select
ActiveSheet.Paste