This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to compute
a formula using the same cell across multiple worksheets but can't figure out
how to do this. I have tried several queries using the search function using
key words but haven't been able to find the answer. Any help will be much
appreciated.

Various ways - what are the sheet names like?

--
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
...
This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to
compute
a formula using the same cell across multiple worksheets but can't figure
out
how to do this. I have tried several queries using the search function
using
key words but haven't been able to find the answer. Any help will be much
appreciated.

the sheet names are account numbers and I am trying to extract the account
name, the account number and the account total (eg. cells A1, A2, A3) into a
list on a new worksheet. Do you you know how to do this?
moushami

"Ken Wright" wrote:

Various ways - what are the sheet names like?

--
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
...
This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to
compute
a formula using the same cell across multiple worksheets but can't figure
out
how to do this. I have tried several queries using the search function
using
key words but haven't been able to find the answer. Any help will be much
appreciated.

Maybe you like this

You can use this macro to create a list on a new worksheet with formulas that link to the
cells on each sheet.
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mnirula" wrote in message ...
the sheet names are account numbers and I am trying to extract the account
name, the account number and the account total (eg. cells A1, A2, A3) into a
list on a new worksheet. Do you you know how to do this?
moushami

"Ken Wright" wrote:

Various ways - what are the sheet names like?

--
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
...
This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to
compute
a formula using the same cell across multiple worksheets but can't figure
out
how to do this. I have tried several queries using the search function
using
key words but haven't been able to find the answer. Any help will be much
appreciated.

there isn't an easier way??? i just want to copy the same cell in many
worksheets into a list. I know how to do a 3-d reference and this seems like
it should be a far easier task. Why is it so complicated?
regards
moushami

"Ron de Bruin" wrote:

Maybe you like this

You can use this macro to create a list on a new worksheet with formulas that link to the
cells on each sheet.
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mnirula" wrote in message ...
the sheet names are account numbers and I am trying to extract the account
name, the account number and the account total (eg. cells A1, A2, A3) into a
list on a new worksheet. Do you you know how to do this?
moushami

"Ken Wright" wrote:

Various ways - what are the sheet names like?

--
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
...
This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to
compute
a formula using the same cell across multiple worksheets but can't figure
out
how to do this. I have tried several queries using the search function
using
key words but haven't been able to find the answer. Any help will be much
appreciated.

You must create a formula link or copy the cells with a macro
http://www.rondebruin.nl/copy2.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mnirula" wrote in message ...
there isn't an easier way??? i just want to copy the same cell in many
worksheets into a list. I know how to do a 3-d reference and this seems like
it should be a far easier task. Why is it so complicated?
regards
moushami

"Ron de Bruin" wrote:

Maybe you like this

You can use this macro to create a list on a new worksheet with formulas that link to the
cells on each sheet.
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mnirula" wrote in message ...
the sheet names are account numbers and I am trying to extract the account
name, the account number and the account total (eg. cells A1, A2, A3) into a
list on a new worksheet. Do you you know how to do this?
moushami

"Ken Wright" wrote:

Various ways - what are the sheet names like?

--
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
...
This is a very simple task. I simply want to create a summary list in a
column of the exact same cell in multiple worksheets. I know how to
compute
a formula using the same cell across multiple worksheets but can't figure
out
how to do this. I have tried several queries using the search function
using
key words but haven't been able to find the answer. Any help will be much
appreciated.

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

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

You can do it manual if you have 3 sheets like this
Enter the = sign in a cell in the first worksheet and select a cell in the second worksheet

You get a formula like this
=Sheet2!A1
Do the same for the other cells

If you have a lot of sheets then my macro is easier
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"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

really? there's no easier way to do this than with a macro. I thought I
could put one formula that makes an absolute cell reference to the cell # and
some kind of code to go to the next worksheet each time i copy down. Then I
would enter this into the first cell and then copy down a column. I don't
understand macro formulas as I have never run a macro.
Any other suggestions?
moushami

"Ron de Bruin" wrote:

You can do it manual if you have 3 sheets like this
Enter the = sign in a cell in the first worksheet and select a cell in the second worksheet

You get a formula like this
=Sheet2!A1
Do the same for the other cells

If you have a lot of sheets then my macro is easier
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"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

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

no i don't have a list and sometimes the tab names differ from the account
names. I am trying to pull the names to create this list and then pull the
account totals. I already linked a number manually but it took forever. I'm
really surprised that there doesn't exist a simple function for this task. I
will try using the indirect function and see what happens.
Thanks a lot for your help so far
moushami

"Ken Wright" wrote:

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

Using Ron's macro really is a doddle, and so much quicker and neater. Try
this step by step guide, and it can be applied to almost any macro you see:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for your workbook's name, and when you find it you may
need to click on the + to expand it. Within that you should see the
following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in the following code starting
at the Sub Summary_All_Worksheets_With_Formulas() bit and finishing at the
End Sub bit.

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add

On Error Resume Next
Newsh.Name = "Summary-Sheet"
If Err.Number 0 Then
MsgBox "The Summary sheet already exist in this workbook."
With Application
.DisplayAlerts = False
Newsh.Delete
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub
End If

RwNum = 1
'The links to the first sheet will start in row 2

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1

Newsh.Cells(RwNum, 1).Value = Sh.Name
'Copy the sheet name in the A column

' For Each myCell In Sh.Range("A1,D5:E5,Z10") ' <----Change
the range
For Each myCell In Sh.Range("B2:D2")
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell
End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / Summary_All_Worksheets_With_Formulas


If you then want to get rid of the macro, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just hit no. Then hit File / Close and return to Microsoft
Excel and save the file.

--
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

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

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

That literally is a step by step guide that I use for people that have never
touched a macro before. I'm afraid that other than what we've given you so
far, I guess I'm pretty much out of ideas at this point.

--
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

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