You can do a number of things:-

1) Link them all manually (No thanks)
2) Use Code to do it.
3) Use the INDIRECT function against either an existing list of account
names, or if the account names are numeric then create the account names
automatically and then use the INDIRECT function to generate links..

Please can you tell me, do you have a list of the account names that are
identical to the tab names for your sheets?

Assume you have a list of account names, and they MUST be identical to your
tab names for this to work. Further assume that those names are listed in
your summary sheet in cells A2:A20 and that you wanted to get the data from
cells A2:C2 from each sheet and put it in cells B:Dx in your summary sheet.

In cell B2 put =INDIRECT("'"&A2&"'!A"&ROW()) and copy down to B20
In cell C2 put =INDIRECT("'"&A2&"'!B"&ROW()) and copy down to C20
In cell D2 put =INDIRECT("'"&A2&"'!C"&ROW()) and copy down to D20

That should work. What's happening is that as an aexample assume the string
in A2 was the name of a sheet (ie your account name) and that it was acct
xyz. If you linked directly to that cell you would create a link such as
='acct xyz'!B2

What you now have to do is build those links using strings and then use the
INDIRECT function to convert a string to a reference. The "'"&A2 bit will
give you 'acct xyz and the "'!A"&ROW() bit will give you '!B2 and by
concatenating them using & you end up with 'acct xyz'!B2. You then wrap the
INDIRECT function around the formula that gives you that and it then becomes
a real reference. Using the ROW() function to generate the 2 in that
formula, allows you to copy down and have the formulas automatically adjust
as the number changes as a result of what ROW() returns.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"mnirula" wrote in message
...
hi ken
maybe I'm not expressing myself clearly. Lets say I have three worksheets
named account a, account b, and account c. I insert a worksheet in the
beginning of the workbook. I want to copy cells A1, A2, and A3 from all
three worksheets into the newly inserted worksheet in a column or a row.
What is the formula that allows me to do this instead of copying and
pasting
from each individual worksheet which can be quite time consuming? Any
suggestions?
Thanks
moushami

"Ken Wright" wrote:

If you'd care to give an example of a couple of sheet names, (or you
perhaps
have a list of these accounts that you can use) an account number (By
all
means dummy it up, but make sure it is representative) then maybe I can
give
you a formula example.

One option may be to use the INDIRECT() function that allows you to
build
range references from strings of data. If you already have a list of
the
accounts (which I assume are the exact sheet names), then we can
probably
help.

--
Regards
Ken.......................

<snip