[Solved] Subtotals - inserting into merged ranges possible?

[eopens]

While trying to create monthly subtotals in a spreadsheet with sales data I get this error:

"Inserting into merged ranges not possible."

I guess this normally means that Calc can't insert subtotals because one or more cells have been merged. However, there are no merged cells! Just to check that I'm not mad I got me old notebook out and used the same spreadsheet (saved as an. xls document) in Excel. It worked as expected; all data was sorted by month with subtotals for the various columns I'm interested in.

Any ideas why Calc comes up with this error?

[Zizi64]

However, there are no merged cells!

Can you upload an example file here?
(See "How to attach a document here?": [Forum] How to attach a document here.)

[eopens]

Thanks for your quick reply... file should be there!

What I'm trying to achieve is this:

1 - Use subtotals based on the 'Month' column
2 - Apply subtotals to the columns named Item A, Item B, Item C, Item D, Item E, Item F, Item G, Item H, Total, Postage, Fee and Profit.

Thanks in advance.

[Charlie Young]

I didn't see any merged cells either, but if I just selected the entire sheet, then clicked Format > Merge Cells, it let me do the subtotals. See Attached

[Zizi64]

I found two merged cell-pairs: AB26-AC26 and AB36-AC36

[eopens]

That must have been the only column I didn't check... it works a treat now. It would nice if there was an easy way of identifying merged cells (I guess you went through the speadsheet cell by cell?) in particular as the original spreadsheet has over 5,000 cells (guess I'll encounter some more merged ones).

Anyway, many thanks for helping me out, much appreciated.

[Zizi64]

First i checked that:
When I select (highlight) all of cells on a spreadsheet, and the menu item: Format-> Merge cells -> Split cells is not grayed, then THERE ARE merged cells.

Yes, then I went through the speadsheet cell by cell.
I do not know any built-in method for search merged cells.

A workaround tip:
If you fill with color the background of one column, clearly visible on the merged cells (double wide color cells) ((Then you need delete the background color.))

[MrProgrammer]

However, there are no merged cells!

Since there are not supposed to be any merged cells in the sheet you can ensure that in Calc by selecting all the cells, opening the Format menu, and clearing the checkmark by Merge Cells if it's present. This would unmerge any merged cells, though it doesn't show you where they are.

If you need to find them, select half of the columns containing your data and look at the Merge Cells setting. Let's say you have 24 columns of data. Select the first half, A-L, and look at Format, Merge Cells. If it's on, a merged cell is in those columns. If it's off, select M-X and check Merged Cells. In either case you have reduced the search from 24 columns to 12. Similarly you can go from 12 to 6, then 6 to 3, etc. Once the guilty columns have been located you can repeat the process on the rows. This process is pretty efficient if there are only a few merged cells in the sheet.

Another way to find merged cells is to save a copy of the spreadsheet as XML with File, Save As, Microsoft Excel 2003 XML. This is really a text format. Close the spreadsheet and with a text editor, open the XML file and look for "ss:Merge" then search backwards to find the "Cell ss:Index" and "Row ss:Index" values that identify where the merged cell is. Or instead of a text editor, rename the file from .XML to .TXT and open it with Writer to look for "ss:Merge".