Sheet Function

[mwollert]

I am trying to get the index of each sheet in a workbook. For test purposes I have a work book with 5 worksheets named "test", "2019 Budget", "2020 Budget", "2021 Budget" and "2022 Budget".

On "test" sheet the names of the the 4 budget sheets are listed in A1:A4

I want to calculate the SHEET INDEX (order of sheet) of each SHEET NAME in A1:A4 in B1:B4. I am entering the following formula in B1:B4:


=SHEET(INDIRECT(A1 & "!A1"))

B1:B4 all show a #REF error.


Any suggestions on what I am doing wrong?

 

[jasonb75]

Your sheet names contain spaces so you have to wrap them in single inverted commas.

=SHEET(INDIRECT("'"&A1 & "'!A1"))

 

[mwollert]

Thank you very much. That did the trick!