Help!

Formula in a sheet reference

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  Charts - help 43  
Author Message
KimC
External


Since: Apr 03, 2006
Posts: 18



PostPosted: Fri Mar 23, 2007 4:49 pm    Post subject: Formula in a sheet reference
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

Is it possible to design a formula that would reference a particular sheet
based on the value in a cell. For example, I want my "master" sheet to pull
data from another sheet whose name is the same as the value of a cell on the
"master" (i.e. on the master, cell A5 contains the value 3 and I want the
other data in that row to come from a sheet named "3").
Back to top
bj
External


Since: Mar 03, 2006
Posts: 1035



PostPosted: Fri Mar 23, 2007 5:01 pm    Post subject: RE: Formula in a sheet reference [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

indirect()
=indirect(A5&"!B1")
would get the data in sheet 3 cell B1 if 3 is in A5

"KimC" wrote:

> Is it possible to design a formula that would reference a particular sheet
> based on the value in a cell. For example, I want my "master" sheet to pull
> data from another sheet whose name is the same as the value of a cell on the
> "master" (i.e. on the master, cell A5 contains the value 3 and I want the
> other data in that row to come from a sheet named "3").
Back to top
Don Guillett
External


Since: Jan 04, 2006
Posts: 3013



PostPosted: Fri Mar 23, 2007 7:04 pm    Post subject: Re: Formula in a sheet reference [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Look in the help index for INDIRECT

For cell a4 in the sheet in cell a5
=INDIRECT(A5&"!"&"A4")

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"KimC" wrote in message

> Is it possible to design a formula that would reference a particular sheet
> based on the value in a cell. For example, I want my "master" sheet to
> pull
> data from another sheet whose name is the same as the value of a cell on
> the
> "master" (i.e. on the master, cell A5 contains the value 3 and I want the
> other data in that row to come from a sheet named "3").
Back to top
KL
External


Since: Feb 24, 2007
Posts: 32



PostPosted: Fri Mar 23, 2007 9:05 pm    Post subject: Re: Formula in a sheet reference [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

just to make the formula a bit more error-proof, I would add the aphostrophes:

=INDIRECT("'"&A5&"'!A4")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


"Don Guillett" wrote in message
> Look in the help index for INDIRECT
>
> For cell a4 in the sheet in cell a5
> =INDIRECT(A5&"!"&"A4")
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "KimC" wrote in message
>
>> Is it possible to design a formula that would reference a particular sheet
>> based on the value in a cell. For example, I want my "master" sheet to
>> pull
>> data from another sheet whose name is the same as the value of a cell on
>> the
>> "master" (i.e. on the master, cell A5 contains the value 3 and I want the
>> other data in that row to come from a sheet named "3").
>
>
Back to top
KimC
External


Since: Apr 03, 2006
Posts: 18



PostPosted: Tue Mar 27, 2007 10:53 pm    Post subject: Re: Formula in a sheet reference [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thank you so much--I would never have found the INDIRECT function in Help.
It works just like I want it to.

"KL" wrote:

> just to make the formula a bit more error-proof, I would add the aphostrophes:
>
> =INDIRECT("'"&A5&"'!A4")
>
> --
> KL
> [MVP - Microsoft Excel]
> RU: http://www.mvps.ru/Program/Default.aspx
> ES: http://mvp.support.microsoft.com/?LN=es-es
> EN: http://mvp.support.microsoft.com/?LN=en-us
> Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
>
>
> "Don Guillett" wrote in message
> > Look in the help index for INDIRECT
> >
> > For cell a4 in the sheet in cell a5
> > =INDIRECT(A5&"!"&"A4")
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "KimC" wrote in message
> >
> >> Is it possible to design a formula that would reference a particular sheet
> >> based on the value in a cell. For example, I want my "master" sheet to
> >> pull
> >> data from another sheet whose name is the same as the value of a cell on
> >> the
> >> "master" (i.e. on the master, cell A5 contains the value 3 and I want the
> >> other data in that row to come from a sheet named "3").
> >
> >
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum