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

[Kedar Agarkar]

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]

=INDIRECT(B1&"!A1212")


"Kedar Agarkar" skrev:

 

[Dave Peterson]

Sometimes, sheet names need to be surrounded by apostrophes.

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

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

=INDIRECT(B1&"!A1212")

"Kedar Agarkar" skrev:

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

 

[Kedar Agarkar]

Thanks 'excelent' and Dave for your help.

Regards

- KA

Sometimes, sheet names need to be surrounded by apostrophes.

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

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

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