Can I reference a worksheet by its position instead of its name?

I have been using Excel for years, but I have just started a project where, for the first time, I am using multiple worksheets. The first worksheet is named "Summary." The rest of the worksheets are named for companies that I receive payments from (4 or 5 different companies). If I create a new company worksheet, I will manually sort the tabs into alphabetical order (except for the summary which will alway be first). In cell A1 of each sheet I will be copying the name of that worksheet from the tab below. All of this I can do with no problem.

On the summary page, I want to put the names of the companies in Column A with one company on each line. These names will appear starting on row 4 with name from the second tab, row 5 with name from third tab, etc. I can get this info from either the name of the tab or from cell A1 on that worksheet which will be the same name.

Problem: I can't figure out how to reference the tabs by their position so that the names will be sorted in the same order on the summary worksheet as are the worksheet tabs. Is there a way to do this? I do not know VB or how to implement it. I am hoping there is a way to do this just using Excel keywords.

Any help greatly appreciated!

I will be pulling in a lot of column totals from the various company pages as well. If I can't reference the worksheets by their position, I would have to hard code all the names into the various cells. There will also be additions and deletions from the companies from time to time. I am trying to set it up so that the only manual action I will have to take is to sort the tabs and change the names on the tabs. It may be that what I am trying to do is not possible without using VB or similar.

Thanks for your reply.

Obviously I did not make the problem clear. Sorting is not a problem. The problem is that I need to pull data from the various worksheets based on their position in the tabs at the bottom of the workbook. When I complete the spreadsheet, I will be pulling data from several different cells on each sheet. I don't want to hardcode the names into each cell formula.

I need a way to reference the cell by its position in the tab list. Is this possible in Excel?

Obviously I didn't make it clear what i am looking for. Sorting the names is not a problem. I need to get several items of data from each sheet and place it on the summary sheet. I don't want to hard code the name on the tab in all the cells that I am pulling data into. The names of the tabs will change from time to time. I just want a way to get a value from a particular cell on the 2nd (or 3rd, etc.) sheet. I need something like =(sheet(3), Cell(C4)).

Is it possible to reference a worksheet by its position in the list at the bottom of the spreadsheet?

See attachment to check if this is usefull to you.

What you want can be done, but the process is a little convoluted.

First you create a named range that will generate an array of sheet names, for example,

Name:- SheetList
Refers to:- =GET.WORKBOOK(1,"Book1.xlsm")

Now you have an array of sheet names to work with, you can use thgis to generate indirect references in your summary sheet.

=INDEX(SheetList,1) will return the name of the summary sheet as this is in position 1, to make it useful, you would need to convert it to a valid range using indirect. The ROWS() counter used below will start the array at sheet2.

=INDIRECT("'"&INDEX(SheetList,ROWS($A$2:$A2)+1)&"'!A1")

You will need to save your workbook as Macro Enabled.

Does this give you something you can work with?