Reference to next non-blank cell

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

Hi Hilvert

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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

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

"Roger Govier" wrote:

Hi Hilvert

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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) 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

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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

"Roger Govier" wrote:

Hi Hilvert

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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) 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

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

"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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

"Roger Govier" wrote:

Hi Hilvert

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

--
Regards
Roger Govier

"Hilvert Scheper" wrote in
message ...
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) 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

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



"Hilvert Scheper" wrote:

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