Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to next non-blank cell
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to next non-blank cell
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to next non-blank cell
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to next non-blank cell
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to next non-blank cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
returning blank when reference cell is blank | Excel Worksheet Functions | |||
how to get excel to display blank if reference cell blank | Excel Worksheet Functions | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
reference a blank cell | Excel Worksheet Functions |