Summary Sheet (Problem with TAB names)

[TurTlepi]

We use Excel for invoices. Each tab is an invoice. I want to create a summary sheet that shows the Invoice #, Company Name, Amount Due

Fortunately the data is in the same cells on each of the sheets.
My question is, how can I create the summary sheet without having to link to each sheet manually.

Unfortunately the Tab names are not simple and cannot be reformatted this year.

A tab name looks like:

332 001 (Vender Name)
332 002 (Next Vendor Name)
Etc

I was thinking maybe =INDIRECT() but doesn't seem to work the way I think...

[sourabhg98]

Can you attach a sample workbook, so that we can have a look at your setup and give solutions more precisely?
You can attach a sample workbook by clicking on "Go Advanced" button at the foot of the typing area and then clicking on the "Paper Clip" icon from the tools above...

[TurTlepi]

Added to original

[sourabhg98]

Try this macro--
Some important points to be kept in mind-
1. Summary sheet should have the first position among all tabs.
2. Click run to get summary, and same for the update. If you add on more sheets or make some changes in the existing ones you just need to press the "Run" button again to update fully.

[Pete_UK]

You can list the sheet names somewhere (e.g. in column G, starting with G3), then you can have this formula in C3:

=INDIRECT("'"&G3&"'!G43")

Copy down as required.

If you don't want to set up a list of names, then assuming your sheet names are consistent with your example, then you can do this in C3:

=INDIRECT("'"&A3&" ("&SUBSTITUTE(B3," Company","")&")'!G43")

and copy down.

Hope this helps.

Pete