Re: [libreoffice-users] Calc: how to auto-increment sheet names in a cell formula?

Hi,
You could name the location of the data on each sheet with a name
created in a way that makes it easy to figure out the name from the row
number. You can then use the INDIRECT() function to retrieve the
information you want.

For example, on the second sheet, the name of the range could be
Source2. You would retrieve the information like this if you have the
formula on row 2:

=INDIRECT(CONCATENATE("Source",ROW())

This will return a reference to the full range, you can then use the
INDEX() function to retrieve the individual values from Ax to Ex.

I hope this helps.
Rémy.

Le mardi 19 septembre 2023 à 23:06 -0700, Tom Cloyd MS MA a écrit :
> 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!
>
> -- 
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~
> “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) | tc@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+unsubscribe@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