Fill Handle to adjust Sheet not cell in a formula

Hello,

I feel like this should be straight forward, but it has bamboozled me. I think I am overcomplicating or missing something obvious.

I use Excel sheets for invoice. 1 sheet = 1 invoice. At the end of the year, I'd like to see a summary of a few cells, like the total.

To do this I am creating a new sheet called "Summary." The A column is the invoice number from cell E2 of the sheet. The invoice number is always E2.

The Ultimate Goal will be a summary page that looks like:

Invoice Number (Typed heading)
1 (all the numbers are referenced cells with the ability to drag down and add more as they appear)
2
3
4
5
6


The cell remains constant but the Sheet changes.

Since the source of the information is a constant, my current formula is straight forward. =Invoice1!$E$2.

I don't want to type =Invoice2, 3, 4, 5, as there are hundreds. However, when I use the Fill handle, it just copies the cell($E$2). So When I drag down it will repeat =Invoice1!$E$2 instead of becoming =Invoice2!$E$2 ,=Invoice3!$E$2, =Invoice4!$E$2 etc.

Ultimate question:
How do I import individual cell information from many sheets onto one sheet without typing into hundreds of cells?


Stuff I have tried:

1) Making 10 cells then filling with ctrl/shift/double click. This does not change the sheet.
2) Experimenting with tables. I think I need to learn more about this process.
3) Macros with recording of clicks just recopies the same sheet.
4) =Indirect/=Xlookup and dragging down still does not change the sheet, just copies.

Thank you.

So i have the list of data, but the source is not a table. I want the output information to become a table that I can filter. Another cell of information that I want is if the client is "new." That information is always in cell E34. I Vstacked all the invoices but I want to use a table to just click and see only the new clients. When I make a table it gives a #SPILL error.

When I try to make a table with this functionality, it won't let me make a table from the Vstacked information. Any way to sort this information with the functions of a table?