I am replacing the sheet names with INDIRECT() in existing formulas of my workbook, to avoid #REF errors when I delete the sheets being referenced. My Existing formula referencing directly the sheet
=NUMBERVALUE(FILTER(INDEX('Complete-BOM'!$A$2:$AZ$3000;SEQUENCE(ROWS('Complete-BOM'!$A$2:$AZ$3000));$B$1);'Complete-BOM'!$A$2:$A$3000<>""))
current formula with INDIRECT
=NUMBERVALUE(FILTER(INDEX(INDIRECT("'"&A10&"'!"&"$A$2:$AZ$3000");SEQUENCE(ROWS(INDIRECT("'"&A10&"'!"&"$A$2:$AZ$3000")));$B$1);INDIRECT("'"&A10&"'!"&"$A$2:$A$3000")<>""))
Replacing with INDIRECT() in the current formula however is causing a #VALUE error due to
SEQUENCE(ROWS(INDIRECT("'"&A10&"'!"&"$A$2:$AZ$3000"))) & INDIRECT("'"&A10&"'!"&"$A$2:$A$3000")
Any suggestions would be a great help