Apache OpenOffice Community Forum

I've searched for an answer to this but cannot find it - sorry if this is old territory (I'm new to the forum).

I have a large spreadsheet (in Lotus 123 as it happens) that I want to move to a more modern machine (the program is 16bit and it is a struggle to get this to work post XP) I'd also like to migrate to Open SUSE if I can crack the issue I'm having.

The sheet is broken down into hundreds of individual ledgers of multiple rows that have a standard format, each "ledger" has multiple columns that have a summation formula for each column in the last row of each "ledger" and this last row is tagged with a simple string "Total" (unsuprisingly !). In Lotus I can run a pseudo sql command that is built into Lotus to find all these rows that begin "Total" and copy the results elsewhere in the sheet (I can then copy these out and manipulate, ru nreports etc). The important thing is that I get the actual calculated totals (i.e. the cell VALUES) as they are displayed.

I've tried to recreate this in Calc using filters and the "copy to" option. No matter what I try I get an actual copy of the row (i.e the embedded formula for totalling the columns) and not the data (VALUE) ?

Am I missing something blindingly obvious ?

Thanks for any help you can give.

Edit>Paste-Special (Insert Key)... text, dates, values.

Thanks for taking the time to answer but .........

Paste (special) is fine and works if I manually copy/cut & paste however I'm running a filter over the whole sheet and selecting the "copy to" feature of the filter. What I need to be able to do is apply the features of the paste special to the copy and I don't know how to do that ?

Cheers.

Sorry, but you've got to filter in place and then copy&paste-special the filtered rows to some other place. Anything more simple would require a database (which may be based on your spreadsheet though).
It could be worth an enhancement request that filter-to-other-place should copy values without formulas because this is what everybody wants when he misuses a spreadsheet as a database

Thanks for the speedy reply.

OK, I understand what you are saying and this does work although a little clunky - maybe I could record a macro to do all the various manual steps. Something else to learn in Calc

Yes, I agree that the use is mixing up databases and spreadsheets but that is just a function (in this case) of the tool (lotus123) that was available back in 1994, how the spreadsheet was "designed" and how it evolved to match the tool/features available and has worked happily ever since.

I cannot see it would do any harm to add another tick box to the filter dialogs to provide this feature - it seems like the infrastructure is probably already there so it may be an easy addition and in my experience this form of a filter has been very useful (despite it mixing metaphores - database/spreadsheet).

How to ask for a modification - ah, there is another question ......

Kind regards.

A recorded macro may do this. I would always prefer to do this single extra step which takes less then a second (Ctrl+V, Insert, Enter).