Since the only difference appears to be the name of the referenced
worksheet(s), you could look at selecting all the cells on a copied/other
existing sheet and changing the name of the sheet used in the formulas using
the Edit | Replace function with the "look in formulas" option selected.

"Naida T" wrote:

Dear Excel experts,

I am stuck with a problem here which I am trying to solve manually, but
knowing Excel, there MUST be a more efficient (time-saving and less prone to
mistakes) way to deal with this.

The essence of the problem is as follows:
- Let's say I have a workbook with 4 worksheets - Summary, CompanyX,
CompanyY, CompanyZ (in reality, I need to update three workbooks with 100+
sheets quarterly and reflect them in the "Summary" sheet)
- Eeach of the "Company" sheets follows the same format (with most important
information - let's say (a) company name, (b) number of staff, (c) address
and (d) sales - all placed in the same cell locations)
- The summary sheet summarises (1) Companies against (2) No of staff /
Address / Sales etc.

For the time being, I am creating references for company names by pointing
to each individual sheet and have to do the same for the second part but
there is certainly a way to automatise referencing to different sheets?!

I would expect that I'd have to do this manually for the first company but
could then do something like ="CompanyA+1"!A2 (read: look up the same cell,
A2, in one sheet after the "CompanyA) but this is not working. Any ideas what
WOULD work?


Thanks a million, N.