I’d like to print the first column from all the sheets in my spreadsheet. Ideally, they all get concatenated, not on separate printout pages. And Ideally printed in 2 columns, newspaper-style (down the left of the page, down the right of the same page, then on to the next page)
I’m using 7.6.4.1 on Windows 11. Thanks for any advice…
A possible solution with the creation of a new sheet collecting all the first columns. Also, to print multiple pages on a single page, use the printing option “Pages per sheet”.
Print_1st_Columns.ods (19.4 KB)
Here is a solution that prints the first column contents of every sheet in the same document as separate columns for a Results sheet (ignoring the Results sheet itself). The Results sheet is formatted to print in portrait mode (US Letter) with two columns. Formatting for this requires calculating the width of the sheet within margins and setting Result sheet column widths to half of that.
The example document attached is set up for a maximum of 10 sheets and a maximum of 1000 rows per sheet, but the print range on the Results sheet is set for 10 sheets and just 100 rows. Notice that ROW(A1:A11) really just sends the numbers 1 to 11 to the SHEETNAME() macro. The results sheet is the “11th” sheet, that’s why A11, not A10.
So the SHEETNAME() macro is required, and here it is hacked from its general usage, so don’t expect the coding to be very clear. The macro results are shown in an array formula (Ctrl+Shift+Enter) across the top. Then separate array formulas are used going down each column, based on the INDIRECT() function.
Don’t be surprised if this requires hard recalculation to update at times (Ctrl+Shift+F9). That said, I was able to open the Results sheet in a new window and watch it update edits in the first window in real time, even changing sheet names. So with some rework the principle could be used for a master scoreboard by putting the Results sheet on a big screen, for example, while the results are being entered on individual sheets.
I know you may be thinking of a different text flow. If so, comment on this result for the community to look at, but also include an idea of how many rows are in a given sheet and how many sheets are in question.
CollectFirstColumns.ods (19.0 KB)