Sheet name, creating dynamically to be used in formula....

Kedar Agarkar

unread,

Jan 1, 2008, 4:40:58 AM1/1/08

to

Hi,

ENV: Excel 2003 running on Windows XP

[SUMMARY]:

I have a cell carrying varying string literal that may refer to sheet
name. Not getting how to use this literal to use in other cell
formulas to refer to sheet name in <<sheet name>>!A1212 style.

[IN DETAIL]:

Seemingly simple but I faultered against this one.

I have in cell say B1, the string literal, refering to sheet name.
Lets say current value of literal is "Stocks" which refer to Stocks
sheet in my XLS.

I wish to use elsewhere formula say something like ='Stocks'!A1212.

For specific needs I need to pick sheet name from B1 and form above
formula string dynamically. However, writing formula in a template
alike manner viz: 'B1'!A1212 or B1!A1212 do not work. So basically I
wish to know how do I create sheet name from contents of B1, and use
in formulas such as one above, keeping to original formula template-
alike.

Tried using INDIRECT etc functions and few probes, but did not get
clue.

Thanks for your time, any pointer shall be of much use.

- KA

excelent

unread,

Jan 1, 2008, 5:11:13 AM1/1/08

to

=INDIRECT(B1&"!A1212")

"Kedar Agarkar" skrev:

Dave Peterson

unread,

Jan 1, 2008, 10:51:01 AM1/1/08

to

Sometimes, sheet names need to be surrounded by apostrophes.

=INDIRECT("'" & B1 & "'!A1212")

If they aren't needed, this formula won't mind.

--

Dave Peterson

Kedar Agarkar

unread,

Jan 2, 2008, 1:46:23 AM1/2/08

to

Thanks 'excelent' and Dave for your help.

Regards

- KA

On Jan 1, 8:51 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Sometimes, sheet names need to be surrounded by apostrophes.
>
> =INDIRECT("'" & B1 & "'!A1212")
>
> If they aren't needed, this formula won't mind.
>
>
>
>
>
> excelent wrote:
>
> > =INDIRECT(B1&"!A1212")
>
> > "KedarAgarkar" skrev:
>
> > > Hi,
>

> > > ENV:Excel2003 running on Windows XP

>
> > > [SUMMARY]:
>
> > > I have a cell carrying varying string literal that may refer to sheet
> > > name. Not getting how to use this literal to use in other cell
> > > formulas to refer to sheet name in <<sheet name>>!A1212 style.
>
> > > [IN DETAIL]:
>
> > > Seemingly simple but I faultered against this one.
>
> > > I have in cell say B1, the string literal, refering to sheet name.
> > > Lets say current value of literal is "Stocks" which refer to Stocks
> > > sheet in my XLS.
>
> > > I wish to use elsewhere formula say something like ='Stocks'!A1212.
>
> > > For specific needs I need to pick sheet name from B1 and form above
> > > formula string dynamically. However, writing formula in a template
> > > alike manner viz: 'B1'!A1212 or B1!A1212 do not work. So basically I
> > > wish to know how do I create sheet name from contents of B1, and use
> > > in formulas such as one above, keeping to original formula template-
> > > alike.
>
> > > Tried using INDIRECT etc functions and few probes, but did not get
> > > clue.
>
> > > Thanks for your time, any pointer shall be of much use.
>
> > > - KA
>
> --
>

> Dave Peterson- Hide quoted text -
>
> - Show quoted text -