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
Bookmarks