Den ons 20 sep. 2023 kl 07:58 skrev Tom Cloyd MS MA <tomcloydm...@gmail.com
>:

> I have trying to solve this problem all evening, with no hint of success:
>
> Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain
> data summarized in cells A1:E1 of each sheet.
>
> I want to capture these summary data in sheet 1, starting with cell
> A1:E1. But I then want to copy those cells into the next row and have
> the references point to the next SHEET - sheet 3.
>
> The general problem is that I have a number of sheets from which I want
> to capture such summary data, but without having to manually edit the
> cell formulas each time to get them to point to the next sheet.
>
> Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to
> the next sheet needs to use some general method of incrementing the
> sheet reference.
>
> Is there an artful way to do this?
>
> Thanks for any ideas offered!
>

Maybe there is a way, but I could only do it by defining my own cell
function. It's very simple, here it is:
Function SheetName(i As Integer) As String
    SheetName=ThisComponent.getSheets().getByIndex(i).getName()
End Function

One way to use it:
=SHEETNAME(1)
The name of your second sheet will be displayed (0 is the first one).

A way to make it change by filling down:
=SHEETNAME(ROW(A1)-1)

If you want the first sheet to be numbered 1, then just change the function:
Function SheetName(i As Integer) As String
    SheetName=ThisComponent.getSheets().getByIndex(i-1).getName()
End Function

Then the last example would look like this:
=SHEETNAME(ROW(A1))

Of course you could make a more advanced function for the whole reference,
that should be doable. Otherwise you can combine this SHEETNAME() function
with functions like ADDRESS() and INDIRECT().
If you input a number higher than the last sheet number you will have an
exception, an error. Of course it's possible to get around that too, for
instance by first counting sheets (I think the total count is already
available, so maybe no counting is needed).


Kind regards

Johnny Rosenberg




>
> --
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> “Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates
> walls
> to arrive at its destination full of hope.” ~ Maya Angelou
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
> Psychological trauma & dissociative disorders, treatment, research, &
> advocacy
> Spokane, Washington, U.S.A. | (435) 272-3332
> https://www.gettraumainfo.com/ (professional)
> Facebook: https://www.facebook.com/groups/645665272216298/
> www.tomcloyd.com/ (personal)
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> System76 Galago Pro (galp5) laptop
> Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
> RAM: 16 GB Dual Channel DDR4 at 3200 MHz
> Storage: 500 GIB SSD
> Operating System: Pop!_OS 22.04 LTS
> Kernel Version: 6.2.6-76060206-generic x86_64
> Gnome ver. 42.5
> Windowing system: X11
> Qt Version: 5.13.3
> [updated 2023-05-09:1420]
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Reply via email to