[Solved] Auto fill formulas across multiple sheets

Discuss the spreadsheet application
Post Reply
SAMURISAM
Posts: 2
Joined: Wed Jul 28, 2021 5:05 am

[Solved] Auto fill formulas across multiple sheets

Post by SAMURISAM »

I'm working with multiple sheets my first sheet is a total sheet I want to auto fill the formulas appropriately so I don't have to go to each sheet . example =Sheet2.E21 is one of the formulas i want the sheet name to change but not the cell as i fill the formulas down so the next entry would be =Sheet3.E21 and then =Sheet4.E21 and on . I'm looking for help on how I can change the formula so when i drag the box to auto fill it changes only the sheet reference . right now it changes the cell reference and the sheet remains the same
Last edited by MrProgrammer on Wed Aug 04, 2021 5:28 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
samurisam open office 4.1.8
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: auto filling formulas across mutiple sheets

Post by Zizi64 »

If you want to use this calculation often, I suggest you to use a macro solution:

Code: Select all

REM  *****  BASIC  *****
option explicit

Function SumAcrossSheets(CellRef as string, ExceptThisSheet as boolean) as double

 Dim oDoc as object
 Dim oSheets, oSheet, oThisSheet as object
 Dim oCell as object
 Dim oController as object
 Dim dSum as double
 Dim i, iCount, iTS_Number as integer 

	oDoc =ThisComponent
	oSheets = oDoc.Sheets
	iCount = oSheets.Count
	
	oController = oDoc.Currentcontroller
	oThisSheet = oController.getActiveSheet
	iTS_Number = oThisSheet.RangeAddress.Sheet
	
	dSum = 0
	For i = 0 to iCount-1
		oSheet = oSheets.getbyindex(i)
		if not((i = iTS_Number) and (ExceptThisSheet)) then
			oCell = oSheet.getCellRangebyName(CellRef)
			dSum = dSum + oCell.Value
		end if	
	next i	
 SumAcrossSheets = dSum
End function
Pass the cell reference to the function as a string: "E21".
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto filling formulas across multiple sheets

Post by Villeroy »

When you insert a new sheet between 2 sheets, Sheet1.X99:Sheet2.X99 will always include the inserted sheet. This reference is expanded when you append a sheet.
If you simply append a blank sheet, hide that blank sheet and include it in your formula, then any new sheet will be inserted before that hiden sheet and the reference Sheet1.X99:HiddenSheet.X99 will always include the new sheet since it is actually inserted rather than appended.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
SAMURISAM
Posts: 2
Joined: Wed Jul 28, 2021 5:05 am

Re: Auto filling formulas across multiple sheets

Post by SAMURISAM »

Is there some way to simply change the way i have written the formula so as i drag to auto fill on my totals page (which is getting it's information from the other sheets) the sheet name is the item in the formula that changes instead of the cell name . Currently if i drag down to auto fill the column the sheet reference in the formula does not change and the cell reference is what changes numerically. i am not a coding person do not have skills in that but am looking for a possible simple solution for this please.
samurisam open office 4.1.8
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Auto filling formulas across multiple sheets

Post by MrProgrammer »

Hi, and welcome to the forum.
SAMURISAM wrote:I want to auto fill the formulas appropriately so I don't have to go to each sheet . example =Sheet2.E21 is one of the formulas i want the sheet name to change but not the cell as i fill the formulas down so the next entry would be =Sheet3.E21 and then =Sheet4.E21 and on.
This is a common question:
[Solved] Change which dimensions are relative with autofill?

If you need any additional assistance, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply