Is there a way to change the sheet name indicated in formulas and not do it one by one?

[PeytPeyt]

I am doing a summary for multiple sheets and have 6 formulas that would point to each sheet. I have already extracted all the sheet names and there are 32 sheet and I need to do this to multiple workbooks, I just need to know if there is a less painful way of changing the sheet names and not change them one by one.

If it helps, here are the formulas
Column D. ='SHEET1'!$B$6
Column E. ='SHEET1'!$E$32
Column F. ='SHEET1'!$B$4
Column G. ='SHEET1'!$G$32
Column H. =IF(ABS(MIN('SHEET1'!$G$13:$G$31))>=MAX('SHEET1'!$G$13:$G$31),MIN('SHEET1'!$G$13:$G$31),MAX('SHEET1'!$G$13:$G$31))
Column I. =XLOOKUP(H21,'SHEET1'!$G$12:$G$20,'SHEET1'!$B$12:$B$20)

 

[Alex Blakenburg]

I normally avoid using Indirect but see it this is heading in the right direction.
PS: Indirect is volatile and you may slow down excel too much if you are talking about 100 sheets.

20230701 Summarise sheets Indirect PeytPeyt.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3	sht name	Date	PO#	No. of Batch	Actual usage	Commodity	Yield %	%Tolerance	Remarks
4	Sheet2			sht2 Batch	0	sht 2 Commodity	0	0	0
5	Sheet3			sht3 Batch	0	sht 3 Commodity	0	0	0
6
Sheet1

Cell Formulas
Range	Formula
D4:D5	D4	=INDIRECT("'" &$A4&"'!$B$6")
E4:E5	E4	=INDIRECT("'" &$A4&"'!$E$32")
F4:F5	F4	=INDIRECT("'" &$A4&"'!$B$4")
G4:G5	G4	=INDIRECT("'" &$A4&"'!$G$32")
H4:H5	H4	=LET(colG,INDIRECT("'" &$A4&"'!$G$13:$G$31"), IF(ABS(MIN(colG))>=MAX(colG),MIN(colG),MAX(colG)))
I4:I5	I4	=XLOOKUP(H21,INDIRECT("'" &$A4&"'!$G$12:$G$20"),INDIRECT("'" &$A4&"'!$G$12:$G$20"))