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

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

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

=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

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

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

"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



--

Dave Peterson

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

Thanks 'excelent' and Dave for your help.

Regards

- KA


On Jan 1, 8:51*pm, Dave Peterson 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 -