Whilst it’s quite easy to just select a number of row or column headers and insert “empties” before or after that point, it first requires the selection of the appropriate number of “targets”.
With an existing CHART range and a judiciously applied “insertion” point then that chart range definition will automatically expand to incorporate the new cells which will appear in the chart.
It’s easier to calculate that inserting enough cells to “capture” 8 new values a day for the new month of November requires 248 new elements than it is to drag the cursor for those 248 insertion points.
Is there a simple solution?
Is it a parameter in one of the myriad personalisation settings or am I the only person in the LOSPHERE who will ever want to perform that function?
Is it a reasonable, potential enhancement request?
OR
Is there a manner in which a chart range can be defined to use labels instead of absolute references?
Why not to use Paste Special option Shift Cells Down.
Why do you use “absolute references” if do you need to add new data?
Perhaps I should have explained, I only create the sheet large enough for the next month and then populate it as it happens. At month end I just grow the sheet to accommodate next month. I suppose I could have initially created a 5 year sheet and just hidden the rows until needed but until v7, LO sucked at hiding rows. Now if I want to create the remaining 4 years I will need a spreadsheet to tell me how many rows to insert and I will still have the problem of “overshooting” when selecting the range on empty cells CTRL+SHFT+DOWN requires a physical “block” if you don’t want to select to the end of the sheet.- more on this below.
Each of the eight event lines for each day and their formulae have to be replicated for the required number of days.
Some of the lines partially replicate the line above and others in the same cluster are unique so, drag filling still requires remediation of the natural tendency of LO to increment numbers on a drag fill.
Each event row supports 30 columns of formulae
Each event has different formatting with back and forward references in those formulae.
All summation is by SUBTOTAL() so I can select and sort daily, weekly, monthly, event, value range anything and the chart responds automatically.
To replicate, I select the eight events and then LO pastes them all into the selected range, but as I said;
CHART forces absolute references when it’s created and if I initially set it up to “overshoot” the range (each column is a labelled range) then it will automatically expand the absolute references as the rows are inserted above the overshoot - so, the procedure is to identify how many rows required and then manually insert that many selected rows into the sheet - inside the overshoot range.
The biggest problem is that dragging an empty selection of rows accelerates like a Saturn V once all the populated cells have scrolled off the monitor and invariably ends up 32572.638 rows further down than I need and dragging back up again then overshoots the existing end of data.
Just think, if I could say please insert 241 rows here then it’s done and dusted and my biggest problem is the easy bit - ensuring the sequencing and sorted links remained intact.
It will be fantastic. I agree. You can post an enhancement request at https://bugs.documentfoundation.org/, and share the bug report number here.
Some workarounds:
-
You can use the Name Box (
Ctrl+Shift+T) to enter the reference of the rows to be added to the chart. So, if you are at row 7532, you can typeA7532:AD7773to select 30 columns and 241 rows at once. First you must calculate 7532+241. -
To select 241 rows, try the menu
Sheet-Fill Cells1.Fill Series…:- Type 1, in the first empty row
- Select down (
Ctrl+Shift+Down Arrow) - And
Fill Serieswith End value: “241” - Select these cells and paste your formula
-
Or, you can try the Insert or delete cells option, and instead of step 4:
- Select these cells and delete their content
- Select these rows (
Shift+Space bar) - Insert new rows (
Ctrl++)
This way, the chart references will include 241 new rows without much effort.
-
Another workaround (a bit stupid, I think) could be to open a new text document (Writer), insert a table with 241 rows, add some content to each cell (i.e., copy an “a”, select all, and paste), copy and paste in Calc.
I shall certainly experiment with your suggestions.
In the meantime, Bugzilla – Bug tdf#145500 Submitted
Bully ;))
They were some truly inspiring suggestions.
Most significant was the ability to use the name box to define and select the “target range”.
It was then simply a case of picking and mixing parts of your suggestions with some of my own experiences and producing a composite workaround.
It works like a charm and the new procedure removes the need to remedy the incremented numbers on a drag fill - I had some VLOOKUP()s that range from 1>4 which had a tendency to grow to 34!
It also highlighted a previously observed bug in conditional formatting management …for which I shall update one of the pre-existing bug reports.
As I mentioned, there are 8 events which have to be replicated and the LO Paste procedure writes a new definition of the conditional formatting for each cluster of 8 new cells. I still have to delete the 150+ surplus entries over 5 columns as it also “doctors” the original record to encompass the extended range.
Another good day - I learned at least two new skills and will simply write a “prompt” into the header area to present me with the new range for “next month”
Thanks
Its pretty simple to create a small makro with inputbox:
sub insertRows
doc = thisComponent
sel = doc.CurrentSelection
' # ToDo:' catch different kinds of Selection
current_row = sel.CellAddress.Row
rows = sel.Spreadsheet.Rows
how_many = inputbox("how many Rows do you want to insert:")
rows.insertByIndex(current_row, how_many )
end sub
@karolus
Thanks for the assist but, I have no idea how to create macros - yet
I (think I) can interpret what it’s doing - it looks to be replacing the manual typing of the range and then the manual inserting of rows.
Once @LeroyG had enlightened me on the functionality of the name box, I found it simple enough to just create a small sequence of cells identifying the “last used” row, the number of days in next month and a factor for the number of elements per day (absolutely fixed) which was just concatenated into a cell producing something like A3497:T3745. This I just cut & paste into the name box and then insert rows from the row header.
Yours is obviously far more elegant than mine and I’ll get around to experimenting when my skill-set is a tad more advanced.
Perhaps I’ll even master a macro for replicating the 8 row array that needs to be pasted into the newly created space AND fixing the mess inserting 30+groups of the new array makes of the conditional formatting register - but I won’t be holding my breath.
Again, thank you for the help.