Calc document with 2 sheets. First sheet receives its data from a Base view_ table and is updated using Data > Refresh Range and then is subjected to conditional formatting. This part works fine. Sheet 2 is a copy of sheet 1 without the header from the table (Row 2 on sheet 1 is row 1 on sheet 2, etc.; this is then copied and pasted into a Writer document to produce a 2 column report). There are 167 rows with 4 columns and I have identified a range of 250 rows for expansion. The problem is that when sheet 1 has additional/new entries, sheet 2 will not expand to record the new rows. It has to be done manually.
Second, if I try to add extra rows manually, I get extraneous data (goofy dates, 0 in text fields where I want blank).
Any insights and help you can give on these two questions will be greatly appreciated.
Joe C
A direct reference to a blank cell always gives zero, but you can avoid that using a matrix reference instead a normal reference.
Introduce the range =Sheet1.A1:D300 and instead [Enter] use [Ctrl+Shif+Enter]
Thank you very much. Using the array approach, and reapplying the conditional formatting, solved both of my listed issues. I thought I has solved the additions not updating, but that didn’t work consistently (something going on that I didn’t understand).
So again, many thanks.
While the matrix approach solved my listed issues, when I copy all of columns A thru D, the paste them into a Writer document as HTML I get the unused rows (in this case from rows 168 thru 265) that I didn’t get before. These extra rows show zeros and a date of 12/31/1899 (my format is 1899/12). Is there a way to stop this other than manually coping specific rows, which defeats my entire process?
How do you paste in writer?
In the Calc sheet, I highlight the header row for columns A:D then on the Menu Edit >Copy. Next I open the Writer document (which is blank except for a pre-formatted header, much like a template) and select from the Menu Edit > Paste Special > HTML. I have adjusted the Calc sheet’s column spacing so that it fits correctly on the Writer document, which is setup with two-columns. Hope this answers your question.