Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Last Cell" to find last row, but can't delete blank rows | Excel Discussion (Misc queries) | |||
sort spreadsheet, "merged cells" comes up. Find cells? Unmerge ? | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |