Sum function question - easy? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Ian in Ankara 2/13/2006 5:44 AM PST
  Question
  Please please help with this simple but long request!!

I have a workbook with multiple named worksheets. In the "summary" worksheet
I have a list of the names of each worksheet. I am trying to use this list to
construct multiple =sum functions each of which refers to a particular named
worksheet, but don't want to type in the name of each worksheet manually.

As an example imagine that there is a worksheet named Leicester and this
name is sitting in cell A1 of the "summary" worksheet. I want to sum the
cells B1 to B5 in the leicester worksheet. I tried to write the following
=SUM(cell("contents",A1)!B1:B5) which does not work.

The problem seems to be how to use the text returned from the cell function
(or t function) as actual text so that it attaches to ! to reference the
appropriate cell.

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Philip J Smith 2/13/2006 6:06 AM PST
  Answer
  Hi Ian

See the help on the "Indirect" function
=SUM(INDIRECT(A1&"!"&"b1:b5"))

Regards.

"Ian in Ankara" wrote:

> Please please help with this simple but long request!!
>
> I have a workbook with multiple named worksheets. In the "summary" worksheet
> I have a list of the names of each worksheet. I am trying to use this list to
> construct multiple =sum functions each of which refers to a particular named
> worksheet, but don't want to type in the name of each worksheet manually.
>
> As an example imagine that there is a worksheet named Leicester and this
> name is sitting in cell A1 of the "summary" worksheet. I want to sum the
> cells B1 to B5 in the leicester worksheet. I tried to write the following
> =SUM(cell("contents",A1)!B1:B5) which does not work.
>
> The problem seems to be how to use the text returned from the cell function
> (or t function) as actual text so that it attaches to ! to reference the
> appropriate cell.
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Ian in Ankara 2/13/2006 7:35 AM PST
   
  Thanks very much for your quick response which has already saved me hours of
typing!

Not to be cheeky, but any idea how to do the same thing and avoiding the
"b1:b5" remaing an absolute reference?

Cheers

Ian

"Philip J Smith" wrote:

> Hi Ian
>
> See the help on the "Indirect" function
> =SUM(INDIRECT(A1&"!"&"b1:b5"))
>
> Regards.
>
> "Ian in Ankara" wrote:
>
> > Please please help with this simple but long request!!
> >
> > I have a workbook with multiple named worksheets. In the "summary" worksheet
> > I have a list of the names of each worksheet. I am trying to use this list to
> > construct multiple =sum functions each of which refers to a particular named
> > worksheet, but don't want to type in the name of each worksheet manually.
> >
> > As an example imagine that there is a worksheet named Leicester and this
> > name is sitting in cell A1 of the "summary" worksheet. I want to sum the
> > cells B1 to B5 in the leicester worksheet. I tried to write the following
> > =SUM(cell("contents",A1)!B1:B5) which does not work.
> >
> > The problem seems to be how to use the text returned from the cell function
> > (or t function) as actual text so that it attaches to ! to reference the
> > appropriate cell.
> >
> >
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 2/13/2006 8:05 AM PST
   
  Us another cell and store the cells in that

=SUM(INDIRECT("'"&A1&"'!"&B1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ian in Ankara" <IaninAnkara@discussions.microsoft.com> wrote in message
news:E8961ECB-36D8-458D-A1D1-28301BA99633@microsoft.com...
> Thanks very much for your quick response which has already saved me hours
of
> typing!
>
> Not to be cheeky, but any idea how to do the same thing and avoiding the
> "b1:b5" remaing an absolute reference?
>
> Cheers
>
> Ian
>
> "Philip J Smith" wrote:
>
> > Hi Ian
> >
> > See the help on the "Indirect" function
> > =SUM(INDIRECT(A1&"!"&"b1:b5"))
> >
> > Regards.
> >
> > "Ian in Ankara" wrote:
> >
> > > Please please help with this simple but long request!!
> > >
> > > I have a workbook with multiple named worksheets. In the "summary"
worksheet
> > > I have a list of the names of each worksheet. I am trying to use this
list to
> > > construct multiple =sum functions each of which refers to a particular
named
> > > worksheet, but don't want to type in the name of each worksheet
manually.
> > >
> > > As an example imagine that there is a worksheet named Leicester and
this
> > > name is sitting in cell A1 of the "summary" worksheet. I want to sum
the
> > > cells B1 to B5 in the leicester worksheet. I tried to write the
following
> > > =SUM(cell("contents",A1)!B1:B5) which does not work.
> > >
> > > The problem seems to be how to use the text returned from the cell
function
> > > (or t function) as actual text so that it attaches to ! to reference
the
> > > appropriate cell.
> > >
> > >


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Arvi Laanemets 2/13/2006 9:19 AM PST
   
  Hi

Another way is to use OFFSET function - but is it usable or not depends on
your data. Something like

=SUM(OFFSET(INDIRECT("'" & A1 & "'!B1"),NumExpr1,NumExpr2,,NumExpr3,))
, where NumExpr1 and NumExpr2 determine the starting cell position
relatively to B1, and NumExpr3 determines the number of cells in summed
range. You are free to use any Excel functions in those expressions, or you
can use a fixed value for any of them. An randomly constructed example:
=SUM(OFFSET(INDIRECT("'" & A1 & "'!B1"),,COLUMN()-2,5,))


Arvi Laanemets



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ecTmyWLMGHA.3104@TK2MSFTNGP11.phx.gbl...
> Us another cell and store the cells in that
>
> =SUM(INDIRECT("'"&A1&"'!"&B1))
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Ian in Ankara" <IaninAnkara@discussions.microsoft.com> wrote in message
> news:E8961ECB-36D8-458D-A1D1-28301BA99633@microsoft.com...
> > Thanks very much for your quick response which has already saved me
hours
> of
> > typing!
> >
> > Not to be cheeky, but any idea how to do the same thing and avoiding the
> > "b1:b5" remaing an absolute reference?
> >
> > Cheers
> >
> > Ian
> >
> > "Philip J Smith" wrote:
> >
> > > Hi Ian
> > >
> > > See the help on the "Indirect" function
> > > =SUM(INDIRECT(A1&"!"&"b1:b5"))
> > >
> > > Regards.
> > >
> > > "Ian in Ankara" wrote:
> > >
> > > > Please please help with this simple but long request!!
> > > >
> > > > I have a workbook with multiple named worksheets. In the "summary"
> worksheet
> > > > I have a list of the names of each worksheet. I am trying to use
this
> list to
> > > > construct multiple =sum functions each of which refers to a
particular
> named
> > > > worksheet, but don't want to type in the name of each worksheet
> manually.
> > > >
> > > > As an example imagine that there is a worksheet named Leicester and
> this
> > > > name is sitting in cell A1 of the "summary" worksheet. I want to sum
> the
> > > > cells B1 to B5 in the leicester worksheet. I tried to write the
> following
> > > > =SUM(cell("contents",A1)!B1:B5) which does not work.
> > > >
> > > > The problem seems to be how to use the text returned from the cell
> function
> > > > (or t function) as actual text so that it attaches to ! to reference
> the
> > > > appropriate cell.
> > > >
> > > >
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 2/13/2006 6:10 AM PST
  Answer
  =SUM(INDIRECT("'"&A1&"'!B1:B5"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ian in Ankara" <IaninAnkara@discussions.microsoft.com> wrote in message
news:249C2227-2E90-47E6-A26D-3599E6C5E7B0@microsoft.com...
> Please please help with this simple but long request!!
>
> I have a workbook with multiple named worksheets. In the "summary"
worksheet
> I have a list of the names of each worksheet. I am trying to use this list
to
> construct multiple =sum functions each of which refers to a particular
named
> worksheet, but don't want to type in the name of each worksheet manually.
>
> As an example imagine that there is a worksheet named Leicester and this
> name is sitting in cell A1 of the "summary" worksheet. I want to sum the
> cells B1 to B5 in the leicester worksheet. I tried to write the following
> =SUM(cell("contents",A1)!B1:B5) which does not work.
>
> The problem seems to be how to use the text returned from the cell
function
> (or t function) as actual text so that it attaches to ! to reference the
> appropriate cell.
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Arvi Laanemets 2/13/2006 6:10 AM PST
  Answer
  Hi

=SUM(INDIRECT("'" & A1 & "'!B1:B5"))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Ian in Ankara" <IaninAnkara@discussions.microsoft.com> wrote in message
news:249C2227-2E90-47E6-A26D-3599E6C5E7B0@microsoft.com...
> Please please help with this simple but long request!!
>
> I have a workbook with multiple named worksheets. In the "summary"
> worksheet
> I have a list of the names of each worksheet. I am trying to use this list
> to
> construct multiple =sum functions each of which refers to a particular
> named
> worksheet, but don't want to type in the name of each worksheet manually.
>
> As an example imagine that there is a worksheet named Leicester and this
> name is sitting in cell A1 of the "summary" worksheet. I want to sum the
> cells B1 to B5 in the leicester worksheet. I tried to write the following
> =SUM(cell("contents",A1)!B1:B5) which does not work.
>
> The problem seems to be how to use the text returned from the cell
> function
> (or t function) as actual text so that it attaches to ! to reference the
> appropriate cell.
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies