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

On 19 Sep 2023 at 23:06, Tom Cloyd MS MA wrote:

Date sent:              Tue, 19 Sep 2023 23:06:41 -0700
To:                     LibreOffice User's Help Forum 
<users@global.libreoffice.org>
From:                   Tom Cloyd MS MA 
<tomcloydmsma@gmail.com>
Subject:                [libreoffice-users] Calc: how to 
auto-increment sheet names in a cell formula?

> 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?

Not 100% sure I understand exactly what you want, but did a test 
macro that does what I think you want. Isn't complex, but mainly a 
process. The Macro uses Page numbers, so it doesn't care what the 
Sheets names are:

Recorded manual process first. Had macro go to cell A30
Then had it copy a1:e1 on second sheet, and go down.
Then copied the lines.
First couple copied the macro lines manually, and modified 
numbers for each arg one by one. Then got smart, and changed 
numbers in blocks of 10 to make it simplier. Just copied the block 
in geany and then changed page number to go to, and then 
changed numbers in next block. Should be obvious.

Only thing might add is to clear the lines on sheet1 since second or 
later times, it gives pop up message since pasting is overwriting 
data. 

sub multisheet2
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = 
createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$30"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Nr"
args5(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args5())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVDFNT"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args6())

rem ----------------------------------------------------------------------
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args9())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Nr"
args10(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args10())

rem ----------------------------------------------------------------------
dim args11(5) as new com.sun.star.beans.PropertyValue
args11(0).Name = "Flags"
args11(0).Value = "SVDFNT"
args11(1).Name = "FormulaCommand"
args11(1).Value = 0
args11(2).Name = "SkipEmptyCells"
args11(2).Value = false
args11(3).Name = "Transpose"
args11(3).Value = false
args11(4).Name = "AsLink"
args11(4).Value = false
args11(5).Name = "MoveMode"
args11(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args11())

rem ----------------------------------------------------------------------
dim args12(1) as new com.sun.star.beans.PropertyValue
args12(0).Name = "By"
args12(0).Value = 1
args12(1).Name = "Sel"
args12(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "Nr"
args13(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args13())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args9())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "Nr"
args15(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args15())

rem ----------------------------------------------------------------------
dim args16(5) as new com.sun.star.beans.PropertyValue
args16(0).Name = "Flags"
args16(0).Value = "SVDFNT"
args16(1).Name = "FormulaCommand"
args16(1).Value = 0
args16(2).Name = "SkipEmptyCells"
args16(2).Value = false
args16(3).Name = "Transpose"
args16(3).Value = false
args16(4).Name = "AsLink"
args16(4).Value = false
args16(5).Name = "MoveMode"
args16(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args16())

rem ----------------------------------------------------------------------
dim args17(1) as new com.sun.star.beans.PropertyValue
args17(0).Name = "By"
args17(0).Value = 1
args17(1).Name = "Sel"
args17(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args17())

rem ----------------------------------------------------------------------
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = "Nr"
args20(0).Value = 5

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args20())

rem ----------------------------------------------------------------------
dim args21(0) as new com.sun.star.beans.PropertyValue
args21(0).Name = "ToPoint"
args21(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args21())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "Nr"
args22(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args22())

rem ----------------------------------------------------------------------
dim args23(5) as new com.sun.star.beans.PropertyValue
args23(0).Name = "Flags"
args23(0).Value = "SVDFNT"
args23(1).Name = "FormulaCommand"
args23(1).Value = 0
args23(2).Name = "SkipEmptyCells"
args23(2).Value = false
args23(3).Name = "Transpose"
args23(3).Value = false
args23(4).Name = "AsLink"
args23(4).Value = false
args23(5).Name = "MoveMode"
args23(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args23())

rem ----------------------------------------------------------------------
dim args24(1) as new com.sun.star.beans.PropertyValue
args24(0).Name = "By"
args24(0).Value = 1
args24(1).Name = "Sel"
args24(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args24())

rem ----------------------------------------------------------------------
dim args30(0) as new com.sun.star.beans.PropertyValue
args30(0).Name = "Nr"
args30(0).Value = 6

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args30())

rem ----------------------------------------------------------------------
dim args31(0) as new com.sun.star.beans.PropertyValue
args31(0).Name = "ToPoint"
args31(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args31())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args32(0) as new com.sun.star.beans.PropertyValue
args32(0).Name = "Nr"
args32(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args32())

rem ----------------------------------------------------------------------
dim args33(5) as new com.sun.star.beans.PropertyValue
args33(0).Name = "Flags"
args33(0).Value = "SVDFNT"
args33(1).Name = "FormulaCommand"
args33(1).Value = 0
args33(2).Name = "SkipEmptyCells"
args33(2).Value = false
args33(3).Name = "Transpose"
args33(3).Value = false
args33(4).Name = "AsLink"
args33(4).Value = false
args33(5).Name = "MoveMode"
args33(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args33())

rem ----------------------------------------------------------------------
dim args34(1) as new com.sun.star.beans.PropertyValue
args34(0).Name = "By"
args34(0).Value = 1
args34(1).Name = "Sel"
args34(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args34())

rem ----------------------------------------------------------------------
dim args40(0) as new com.sun.star.beans.PropertyValue
args40(0).Name = "Nr"
args40(0).Value = 7

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args40())

rem ----------------------------------------------------------------------
dim args41(0) as new com.sun.star.beans.PropertyValue
args41(0).Name = "ToPoint"
args41(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args41())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args42(0) as new com.sun.star.beans.PropertyValue
args42(0).Name = "Nr"
args42(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args42())

rem ----------------------------------------------------------------------
dim args43(5) as new com.sun.star.beans.PropertyValue
args43(0).Name = "Flags"
args43(0).Value = "SVDFNT"
args43(1).Name = "FormulaCommand"
args43(1).Value = 0
args43(2).Name = "SkipEmptyCells"
args43(2).Value = false
args43(3).Name = "Transpose"
args43(3).Value = false
args43(4).Name = "AsLink"
args43(4).Value = false
args43(5).Name = "MoveMode"
args43(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args43())

rem ----------------------------------------------------------------------
dim args44(1) as new com.sun.star.beans.PropertyValue
args44(0).Name = "By"
args44(0).Value = 1
args44(1).Name = "Sel"
args44(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args44())

rem ----------------------------------------------------------------------
dim args50(0) as new com.sun.star.beans.PropertyValue
args50(0).Name = "Nr"
args50(0).Value = 8

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args50())

rem ----------------------------------------------------------------------
dim args51(0) as new com.sun.star.beans.PropertyValue
args51(0).Name = "ToPoint"
args51(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args51())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args52(0) as new com.sun.star.beans.PropertyValue
args52(0).Name = "Nr"
args52(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args52())

rem ----------------------------------------------------------------------
dim args53(5) as new com.sun.star.beans.PropertyValue
args53(0).Name = "Flags"
args53(0).Value = "SVDFNT"
args53(1).Name = "FormulaCommand"
args53(1).Value = 0
args53(2).Name = "SkipEmptyCells"
args53(2).Value = false
args53(3).Name = "Transpose"
args53(3).Value = false
args53(4).Name = "AsLink"
args53(4).Value = false
args53(5).Name = "MoveMode"
args53(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args53())

rem ----------------------------------------------------------------------
dim args54(1) as new com.sun.star.beans.PropertyValue
args54(0).Name = "By"
args54(0).Value = 1
args54(1).Name = "Sel"
args54(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args54())

rem ----------------------------------------------------------------------
dim args60(0) as new com.sun.star.beans.PropertyValue
args60(0).Name = "Nr"
args60(0).Value = 9

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args60())

rem ----------------------------------------------------------------------
dim args61(0) as new com.sun.star.beans.PropertyValue
args61(0).Name = "ToPoint"
args61(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args61())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args62(0) as new com.sun.star.beans.PropertyValue
args62(0).Name = "Nr"
args62(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args62())

rem ----------------------------------------------------------------------
dim args63(5) as new com.sun.star.beans.PropertyValue
args63(0).Name = "Flags"
args63(0).Value = "SVDFNT"
args63(1).Name = "FormulaCommand"
args63(1).Value = 0
args63(2).Name = "SkipEmptyCells"
args63(2).Value = false
args63(3).Name = "Transpose"
args63(3).Value = false
args63(4).Name = "AsLink"
args63(4).Value = false
args63(5).Name = "MoveMode"
args63(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args63())

rem ----------------------------------------------------------------------
dim args64(1) as new com.sun.star.beans.PropertyValue
args64(0).Name = "By"
args64(0).Value = 1
args64(1).Name = "Sel"
args64(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args64())

rem ----------------------------------------------------------------------
dim args70(0) as new com.sun.star.beans.PropertyValue
args70(0).Name = "Nr"
args70(0).Value = 10

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args70())

rem ----------------------------------------------------------------------
dim args71(0) as new com.sun.star.beans.PropertyValue
args71(0).Name = "ToPoint"
args71(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args71())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args72(0) as new com.sun.star.beans.PropertyValue
args72(0).Name = "Nr"
args72(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args72())

rem ----------------------------------------------------------------------
dim args73(5) as new com.sun.star.beans.PropertyValue
args73(0).Name = "Flags"
args73(0).Value = "SVDFNT"
args73(1).Name = "FormulaCommand"
args73(1).Value = 0
args73(2).Name = "SkipEmptyCells"
args73(2).Value = false
args73(3).Name = "Transpose"
args73(3).Value = false
args73(4).Name = "AsLink"
args73(4).Value = false
args73(5).Name = "MoveMode"
args73(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args73())

rem ----------------------------------------------------------------------
dim args74(1) as new com.sun.star.beans.PropertyValue
args74(0).Name = "By"
args74(0).Value = 1
args74(1).Name = "Sel"
args74(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args74())

rem ----------------------------------------------------------------------
dim args80(0) as new com.sun.star.beans.PropertyValue
args80(0).Name = "Nr"
args80(0).Value = 11

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args80())

rem ----------------------------------------------------------------------
dim args81(0) as new com.sun.star.beans.PropertyValue
args81(0).Name = "ToPoint"
args81(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args81())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args82(0) as new com.sun.star.beans.PropertyValue
args82(0).Name = "Nr"
args82(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args82())

rem ----------------------------------------------------------------------
dim args83(5) as new com.sun.star.beans.PropertyValue
args83(0).Name = "Flags"
args83(0).Value = "SVDFNT"
args83(1).Name = "FormulaCommand"
args83(1).Value = 0
args83(2).Name = "SkipEmptyCells"
args83(2).Value = false
args83(3).Name = "Transpose"
args83(3).Value = false
args83(4).Name = "AsLink"
args83(4).Value = false
args83(5).Name = "MoveMode"
args83(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args83())

rem ----------------------------------------------------------------------
dim args84(1) as new com.sun.star.beans.PropertyValue
args84(0).Name = "By"
args84(0).Value = 1
args84(1).Name = "Sel"
args84(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args84())

end sub



> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor 
(Retired)     
 mailto:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




-- 
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