Cumulatively list data from each new worksheet

[David Newmarch]

I have a very simple system for generating invoices where I have one
worksheet as the invoice template, and then for each new invoice I copy that
template to a new worksheet, rename that worksheet with the new invoice
number, and use a formula to copy that sheetname to the relevant "InvoiceNo."
cell on the new worksheet. In other words, each invoice is a separate
worksheet, with the invoice number matching the worksheet name. I then type
in the relevant details for the new invoice and the invoice total gets
calculated.

The cell addresses for four key values on each invoice (same on every
invoice/worksheet) are

InvoiceNo: E5
InvoiceDate: E6
ClientName: B10
InvoiceTotal: F38

So far this suits me fine (there are seldom more than four or five invoices
per month). But what I would like to do now is have an additional summary
worksheet (in the same workbook) which stores a cumulative list of the key
data from each invoice/worksheet, automatically adding a new row for each new
invoice/worksheet as it it created, with columns for InvoiceNo, InvoiceDate,
ClientName, and InvoiceTotal respectively.

It would relatively straightforward for me to set this up this as a simple
database in Access but I'm sure that what I want is also a simple matter in
Excel. I just can't figure out how to go about it.

Any help would be much appreciated.

 

[Jonathan Cooper]

On your summary sheet, I assume you have a column where you list all the
invoice numbers which are conceivable for a year. Let's say that is column
A, with A2 containing the first invoice number. Column B is where you want
the invoice total.

In B2, try this formula to pull the total from each invoice.

=IF(ISERROR(INDIRECT(A2&"!F38")),"Invoice Doesn't Exist",INDIRECT(A2&"!F38"))

NOTE: an accidental space at the end of an worksheet name will give you the
"Invoice Doesn't Exist" error.