Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default How to find non-blank cell values "hidden" under merged regions?

I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,582
Default How to find non-blank cell values "hidden" under merged regions?

That's strange. I've never retained a value from a cell that was merged with
another cell or cells above or left of it. I just tested in a new worksheet,
and the second value is lost upon merging, and in fact you have to approve a
dialog before the merge occurs if it will delete data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default How to find non-blank cell values "hidden" under merged regions?

Hi Jon

FYI you can retain data in a merged cell.
If you merge 2 cells with values, it is possible to get one value to
show, and for the other value to be retained but not visible.
David McRitchie unearthed this some while back whilst trying to resolve
a problem with a workbook from a poster to one of the NG's

This thread should give you the details
http://snipurl.com/1felt

--
Regards

Roger Govier


"Jon Peltier" wrote in message
...
That's strange. I've never retained a value from a cell that was
merged with another cell or cells above or left of it. I just tested
in a new worksheet, and the second value is lost upon merging, and in
fact you have to approve a dialog before the merge occurs if it will
delete data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which
is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with
POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.





  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,582
Default How to find non-blank cell values "hidden" under merged regions?

Well, I had to try it myself before I would believe it. As I said before,
"That's strange." The values hidden behind the pseudo-merged cell are
included in formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Roger Govier" wrote in message
...
Hi Jon

FYI you can retain data in a merged cell.
If you merge 2 cells with values, it is possible to get one value to show,
and for the other value to be retained but not visible.
David McRitchie unearthed this some while back whilst trying to resolve a
problem with a workbook from a poster to one of the NG's

This thread should give you the details
http://snipurl.com/1felt

--
Regards

Roger Govier


"Jon Peltier" wrote in message
...
That's strange. I've never retained a value from a cell that was merged
with another cell or cells above or left of it. I just tested in a new
worksheet, and the second value is lost upon merging, and in fact you
have to approve a dialog before the merge occurs if it will delete data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.







  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,582
Default How to find non-blank cell values "hidden" under merged regions?

This happens in Excel 97 through 2007.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I had to try it myself before I would believe it. As I said before,
"That's strange." The values hidden behind the pseudo-merged cell are
included in formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Roger Govier" wrote in message
...
Hi Jon

FYI you can retain data in a merged cell.
If you merge 2 cells with values, it is possible to get one value to
show, and for the other value to be retained but not visible.
David McRitchie unearthed this some while back whilst trying to resolve a
problem with a workbook from a poster to one of the NG's

This thread should give you the details
http://snipurl.com/1felt

--
Regards

Roger Govier


"Jon Peltier" wrote in message
...
That's strange. I've never retained a value from a cell that was merged
with another cell or cells above or left of it. I just tested in a new
worksheet, and the second value is lost upon merging, and in fact you
have to approve a dialog before the merge occurs if it will delete data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.











  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default How to find non-blank cell values "hidden" under merged regions?

Sorry Jon, I could have added that to save you testing.
At first I did not believe it, but when I tried it, then it behaved
exactly as Dave McRitchie described, in all versions of Excel.

--
Regards

Roger Govier


"Jon Peltier" wrote in message
...
This happens in Excel 97 through 2007.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I had to try it myself before I would believe it. As I said
before, "That's strange." The values hidden behind the pseudo-merged
cell are included in formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Roger Govier" wrote in message
...
Hi Jon

FYI you can retain data in a merged cell.
If you merge 2 cells with values, it is possible to get one value to
show, and for the other value to be retained but not visible.
David McRitchie unearthed this some while back whilst trying to
resolve a problem with a workbook from a poster to one of the NG's

This thread should give you the details
http://snipurl.com/1felt

--
Regards

Roger Govier


"Jon Peltier" wrote in message
...
That's strange. I've never retained a value from a cell that was
merged with another cell or cells above or left of it. I just
tested in a new worksheet, and the second value is lost upon
merging, and in fact you have to approve a dialog before the merge
occurs if it will delete data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that
reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at
least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single
string
visible and centered in the merged region. I discovered that when
my
application reads the cells, it finds that visible value when
reading
the first cell, but it also finds a value in the second cell,
which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden
value
becomes visible.

I'm going to investigate whether I can detect this condition with
POI,
but I'd like to know if there's some way in the Excel interface I
can
detect and fix (remove) these hidden values.











  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default How to find non-blank cell values "hidden" under merged regions?

On Apr 5, 2:34 pm, "david.karr" wrote:
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.


(I thought I already wrote this here.)

If it matters, I was able to detect this condition in my application
using Jakarta POI, and at the same time I'm able to avoid the problem.
I'm now looking through all the merged regions, and all the cells in
each merged region. If there's a cell that's not the top left-hand
corner that has a non-null value, then that value will be "hidden",
and at that point, I can directly change the cell value to "null" for
that cell (although it doesn't write the file back out).

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,582
Default How to find non-blank cell values "hidden" under merged regions?

Good, at least you can defend against this issue.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"david.karr" wrote in message
ups.com...
On Apr 5, 2:34 pm, "david.karr" wrote:
I have a large spreadsheet that uses merged regions extensively. I
wrote an application using the Java Jakarta POI framework that reads
this spreadsheet and generates other data from it. In general, it
works fine. However, I just discovered a curious anomaly in at least
a single merged region in the spreadsheet.

One merged region is 1 row and 2 columns. There is a single string
visible and centered in the merged region. I discovered that when my
application reads the cells, it finds that visible value when reading
the first cell, but it also finds a value in the second cell, which is
an obsolete value that we used to use.

I can verify this data is present if I select and Copy the entire
second column and paste it into an external editor. The hidden value
becomes visible.

I'm going to investigate whether I can detect this condition with POI,
but I'd like to know if there's some way in the Excel interface I can
detect and fix (remove) these hidden values.


(I thought I already wrote this here.)

If it matters, I was able to detect this condition in my application
using Jakarta POI, and at the same time I'm able to avoid the problem.
I'm now looking through all the merged regions, and all the cells in
each merged region. If there's a cell that's not the top left-hand
corner that has a non-null value, then that value will be "hidden",
and at that point, I can directly change the cell value to "null" for
that cell (although it doesn't write the file back out).



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Last Cell" to find last row, but can't delete blank rows [email protected] Excel Discussion (Misc queries) 6 January 9th 07 12:50 AM
sort spreadsheet, "merged cells" comes up. Find cells? Unmerge ? lowell Excel Discussion (Misc queries) 1 August 20th 06 09:10 AM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 11:01 PM
Changing "returned" values from "0" to "blank" LATATC Excel Worksheet Functions 2 October 20th 05 04:41 PM


All times are GMT +1. The time now is 10:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"