Layout Tables - Reference a Single Column? - Excel Referencing Slow

It seems that I can’t reference a single Excel column such that LO will update a table adding entries as new entries are saved in the Excel file as what happens with the reference A1:End.

Is there a reference that I can use that will do A1:Column A End ?

If I enter a reference, say, A1:A10 and only A1:A3 have data then LO will show a table A1:A10 with empty cells.

In an attempt to replicate table referencing for a single column like A1:End, I created a master worksheet that I can edit all my data easily in one place and then created single column worksheets that link to the master worksheet for each category of data. It is these worksheets containing a single column that LO references.

The problem I have is that when I had only one worksheet, updating the table in LO was quite speedy but now with extra worksheets it has become very slow to update my tables.

It doesn’t appear that having a “Column A End” reference would work. LayOut is looking for finite start and end cells in the table to reference. I guess if I were going to do something like this, I would set up the reference for A1:A10 and use Shape Style in LO to get rid of fill and stroke for the table so that empty cells are displayed only as blank space on the page.

As for the multiple sheets issue, does doing the above change that?

FWIW, it seems that making LO look all the way to the end of a sheet ( 1,048,576 rows by 16,384 columns) should be expected to take some time.

Well it turned out the Excel file was a whopping 23Mb!

I blindly followed a method that didn’t specifically mention that using Excel’s paste link and a cell format to hide zero values was going to paste a formula into every cell in the various category columns - I should have realised, obvious.

Restricting the formula to a sensible number of cells has solved the problem.

1 Like