If you use some system of the naming of your worksheets like account a,
account b etc
ore name+number like account1, account2 etc then it is easy

assuming account a b and c etc

=INDIRECT("'account "&CHAR(ROW(97:97))&"'!A1")

copied down

if account1, account2

=INDIRECT("'account"&ROW(1:1)&"'!A1")

copied down

if you have no naming system that is easy to pick up you can download
Morfunc and use

=INDEX(THREED(account1:account3!$A$1),ROW(1:1))

copied down

can be downloaded from here

http://xcell05.free.fr/


this will allow you to have many sheets with different names

--

Regards,

Peo Sjoblom



"mnirula" wrote in message
...
Ken
thanks for all your help. However, I think I need to learn more about
macros before i attempt to do one so I think I will try the tutorial
first.
I still can't believe that such a basic task requires a macro.
Regards
moushami

"Ken Wright" wrote:

Only thing to watch in there was the damn wordwrap

Look for the bit in the code that does this:-

' For Each myCell In Sh.Range("A1,D5:E5,Z10") '
<----Change
the range
For Each myCell In Sh.Range("B2:D2")
ColNum = ColNum + 1

The above text after the ' can be deleted or needs to be tweaked such
that
you get this


' For Each myCell In Sh.Range("A1,D5:E5,Z10")
' Change the range

For Each myCell In Sh.Range("B2:D2")
ColNum = ColNum + 1

or just this

For Each myCell In Sh.Range("B2:D2")
ColNum = ColNum + 1

The bits after the ' are just textual comments, and show you how to put
in
different ranges if necessary

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


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

--------------------------------------------------------------------------
--
<snip