Indirect sheet reference 2nd sheet regardless of name

[muhleebbin]

In VBA there's the sheet reference method Sheets(2) to reference the second sheet from left to right. Is there a way to work a similar reference in a formula? I'd like cells on the first sheet to be able to reference cells on the second - eighth sheets regardless of the names that are on the sheets or the sheet numbers. The formula is needed because users will add and delete sheets on a whim so this would make this user proof.

 

[Legacy 143009]

Short answer, No.

There is no way to refer "next sheet" by only using formulas afaik.

 

[muhleebbin]

Yeah I couldn't find anything.

I did think of a work around by using this page's idea:

How To Generate A List Of Sheet Names From A Workbook Without VBA | How To Excel

www.howtoexcel.org

I put this on the 10th sheet (1 being the report and 2-9 being the 8 sheets needing to reference from). Used rows 1-25 even though I only needed 10 rows (overkill and future proofing it).

Put the following code in a module

Sub recalculate()
  
    Worksheets("Lists").Range("B2:B25").Formula = "=INDEX(SheetNames,RC[-1])"

End Sub

And called the sub in a worksheet activate for sheet1. If anyone renamed/deleted or added a worksheet then it would update the list on the 10th sheet.

Indirect then worked by referencing the cells in B2:B25.

Only flaw I can think of is if someone adds a sheet before sheet1 or moves a sheet left of it. But hopefully no one's that dumb to do that.

 

[Legacy 143009]

Yes, I know it's possible with VBA. I thought you wouldnt prefere

 

[muhleebbin]

Just curious how it would be done via VBA? The VBA was necessary to account for sheet name changes since the formula on the 10th sheet was static.

Ultimately I was trying to get a formula like this to work on sheet1:

=INDIRECT("'" & Lists!B2 &"'!" & "C7")

Where Lists!B2 in this case will always be Sheets(2) regardless of name/sheet number.