[Solved] Spreadsheet cell flow

[Simon Byrne]

Hi, when working in a .xls or .ods spreadsheet, can anyone tell me how to stop cell content flowing over the next cell when the next cell is empty? Thanks.

[Zizi64]

Hi,

If I understtod you problem exactly:

Example for item 2.
type into next cell without brackets: (=" ")

[Simon Byrne]

Hi Zizi64, Thanks for your reply. Yes that would work but i have a 20 column, 1,500 row spreadsheet and i need to see at a glance as i scroll down if a colum has data in it or if it's empty and at the moment the whole colum is full with either genuine data or overflowing data from the previous column so to trawl through and manually spot which calls have no data is very difficult by the time i've done it for all the colums effected and i'm bound to make errors and miss a few. What i am hoping for is to be able to change the format of the cells in the whole column so the data doesn't overflow and shows the little arrow icon to indicate there is hidden data just like it does in your example where the next cell already has data in it. Any ideas?

[Simon Byrne]

Hi Zizi64, I just posted a reply to your reply and now my post has disappeared from the board. Sorry, this is hte first time i've used a forum so i don't know what i'm doing. How do i get my post back on the board? Thanks, Simon

[Zizi64]

What i am hoping for is to be able to change the format of the cells in the whole column

Yes it is possible. Just select the whole column (click on letter (column name)) and apply the desired format, or a predefined cell style.

(I can see three posts from you, now...)

[fst]

Hi,

if you do not rely on the red triangle you may do the following:

- Open the Stylist (F11 Styles and Formatting)
- Choose the cell styles tab
- Right click on the default cell style and select Modify
- Select the Alignment Tab
- Tag 'Wrap text automatically'
- Click Ok
- Close the Stylist (F11 again)

Check that the cell formerly overflowing or showing the triangle now are grown and show the whole cell content.

Frank

[acknak]

I sometimes use the Ctrl+arrow function to quickly check whether a region is actually empty or not.

For example, click in the top cell of a column and press Ctrl+down-arrow. The cursor will move to the next cell with any content, ignoring content that overflows from other columns. If the cursor moves all the way to the end of the sheet, I know that the column is completely empty. Likewise, if it stops at some cell, I know that all the cells above that are completely empty.

[W.C.Lee]

Hi Zizi64, Thanks for your reply. Yes that would work but i have a 20 column, 1,500 row spreadsheet and i need to see at a glance as i scroll down if a colum has data in it or if it's empty and at the moment the whole colum is full with either genuine data or overflowing data from the previous column so to trawl through and manually spot which calls have no data is very difficult by the time i've done it for all the colums effected and i'm bound to make errors and miss a few. What i am hoping for is to be able to change the format of the cells in the whole column so the data doesn't overflow and shows the little arrow icon to indicate there is hidden data just like it does in your example where the next cell already has data in it. Any ideas?

Honestly, I don't sure I will save your time or not.

Insert 1 column each to the right of your 20 columns of data. Merge each pair of cells in the first row, copy and paste (format only) first row to the rest of rows. Text will not flow outside the merge cells.

[Simon Byrne]

Hi Zizi64 & Acnak,
Thank you for your help. I resolved the issue in the end in just a few clicks by setting the cell properties for the whole sheet to "wrap text" with "alignment to top" and then resetting the row heights for the whole sheet to just display one text line high so now i get the red arrows to denote there is hidden text and none of the cells overflow.
Regards, Simon