Ask Your Question

Calc 2nd sheet "range" not updating [closed]

asked 2017-10-11 00:54:26 +0200

Joe Castor gravatar image

updated 2017-10-11 00:56:03 +0200

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-10-16 15:21:06.153379

1 Answer

Sort by » oldest newest most voted

answered 2017-10-11 17:08:52 +0200

m.a.riosv gravatar image

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]

What is an Array

edit flag offensive delete link more


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.

Joe Castor gravatar imageJoe Castor ( 2017-10-12 02:34:52 +0200 )edit

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?

Joe Castor gravatar imageJoe Castor ( 2017-10-12 02:52:10 +0200 )edit

How do you paste in writer?

m.a.riosv gravatar imagem.a.riosv ( 2017-10-12 17:47:12 +0200 )edit

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.

Joe Castor gravatar imageJoe Castor ( 2017-10-12 18:58:49 +0200 )edit

Question Tools

1 follower


Asked: 2017-10-11 00:54:26 +0200

Seen: 35 times

Last updated: Oct 11 '17