Pulling a worksheet name from cell value (custom function needing help)

See http://www.ozgrid.com/VBA/item-worksheets.htm

---

Originally Posted by Kurisu

Hello gurus, I have a question that is probably easy to solve, but has stumped me for the better part of the day


I am trying to create a custom function to extract another cells value and use that as a worksheet name to reference a cell in that worksheet. That sounds too confusing. Let me try again...

In excel, you can type in "=sheet2!a1" and you will get the value of cell a1 from worksheet 2...but what if you know what cell you want on each worksheet, but you want the user to select which worksheet through a drop list?

For example, lets say you have an excel file with three worksheets, with the default names of "Sheet1," "Sheet2" and "sheet 3"


Worksheet 1 has the following cells with data
a1="fox" in bold
a2="12"
a3="9/16/06" in date format
a4="3.0"
b1="=a2/a4"
b2="$7.69" with a light yellow shading fill
b3 ="bird droppings" written in red text
b4 ="A conundrum inside of a riddle" with thick black borders around it


worksheet 2 has the following cells with data:
a1="dog" in green italic text
a2="42"
a3="(12.99)" in red text
a4="yes"
b1="ask me later" with light blue shading fill
b2="No!
b3 ="Lysol"
b4 ="Ralph Wiggums"


Worksheet 3 has the following cells filled:
a1 is a data validation list with two items in it, named "sheet1" and "sheet2"

a2 is where I put my custom function..."=NameSource(SpecificWorksheet,SpecificCell, format switch) where SpecificWorksheet is the name of the worksheet or a cell reference to a text value that is the name of the worksheet, Specificcell is a single cell range, and format switch has two settings: 0 for extracting only the value and 1 for extracting the cell format along with the value.

a2 = "=NameSource(worksheet1, a1, 1) which shows the cell value of "fox" in bold style
a3 = "=NameSource (a1, a3, 1) which shows the cell value of "9/16/06" if the droplist has "sheet1" selected or shows the value of "(12.99)" in red text if the dropmenu has "sheet2" selected
a4 = "=NameSource (a1, b4, 0) shows the cell value of "A Conundrum inside of a riddle" or "Ralph Wiggums" without any formatting, depending on which droplist item is selected.

I can't figure out how to get the function to use a variable as the worksheet name. Any of you experts know how to to do this? Ultimately, I plan on having a woksheet for each month and use one worksheet with this custom function and a drop box to display their data and perform calculations without having the user having to leave the sheet.

Mucho gracias!
Kurisu

checkout my reply to Rena in http://www.excelforum.com/showthread.php?t=576084
(Linking worksheet information by Rena in Excel Miscellaneous)
This has a number of items for crossing worksheets/workbooks.

The Indiirect formula
=Indirect("anytext"&anycell&"anyothertext"&anyothercell)
or
=Indirect(anycell&"anytext"&anyothercell&"anyothertext")
should have worked for you,

Or, for your example, if (text-wise) A1=Sheet1 and A2=Sheet2 and B1=C2, then
=indirect(A1&"!"&B1) will give you Sheet1!C2
=Indirect(A2&"!"&B1) will give you Sheet2!C2

="The person's name is "&INDIRECT(A1&"!"&B1)&" and their address is "&INDIRECT(A2&"!"&B1)

There appears no reason to use other than a simple formula.

---

Originally Posted by Kurisu

Thanks for the fast response Brian! Unfortunately the link provided is out of the scope of what I am trying to do. I want a formula/function that extracts a different cell's value as the worksheet pointer's name so that the said formula/function can retrieve the data from the 2 pieces of information the user has entered.

so if cell a1 had the value "sheet1" in it
and cell a2 had the value "sheet2" in it

The function =namesource(a1, c2, 0) would do the same work as the embedded formula "sheet1!c2

and the function =namesource(a2, e7, 1) would do the same work as the embedded formula "sheet2!e7 plus keep all of the formatting from that e7

Here is a macro free example of how I intend to use it:
link to file here (bah...this free beta upload site is supported by popup advertisements) :S