Is it possible to display a range of cells in a different sheet in LibreOffice calc?

Question

I've been looking around for a while, but could not find a answer.

My budget sheet contains a meta-sheet called "settings". In "settings" there is an area e.g. C10:D20 that I would like to display in all the other sheets (Jan, Feb, March, ...) in the same document. It's okay if the area is displayed read-only but it should be possible to include these fields in formulas like SUM etc.

In the end, it would be similar to referencing single cells in other sheets using "=settings.C10" but for areas.

Does anyone know if this is possible and if so how?

"Named ranges" in Help only tells you how to name a range and in "Link to External Data" the OK button is grayed out, no matter what file and no matter how I select files.

I've tried this in LibreOffice 3.6 and I'm using LibreOffice 4.0 now but to no avail.

Any hints are appreciated! TIA!

Answer 1

The proper way to do it is to give relevant names to all the cells with values in your "settings" range. The scope of the named cells is the whole document so you can use those names in the formulas in all sheets. If you still want to copy the range on to every sheet (just for the display purpose?) you could just reference every cell in your range separately - you have to type in just the top left cell reference then drag fill the rest.

Answer 2

@Scott Thanks, dragging works although you can't directly drag diagonally. It's either first vertically and then horizontally or vice versa

@Alex Thanks for hint. Basically, this seems to be a useful feature although it's not very user-friendly. As a matter of fact, I'm only halfway there... Via Insert -> Names -> Define I can define a range and name it (the Help is not really helpful again or at least uninituitive). But when inserting a name in different sheet via Insert -> Names -> Insert.., the following must be true: In order to get anything sensible out of the defined Name, you have to insert into the absolutely same fields as in the original sheet, otherwise cald will display a generic #VALUE! error In addition to that, calc will only insert one field of the defined NAME, in order to see the rest, you have to drag out vertically and/or horizontally to get all fields.

This is of course not exactly what I had in mind. I really wanted to have to the liberty to display a range from a different sheet in any other place in a different sheet. For that, Scott's approach is better suited.

Thanks all for the help, I appreciate it. Cheers.