+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    09-16-2006
    Posts
    3

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

    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

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    See http://www.ozgrid.com/VBA/item-worksheets.htm

    ---

    Quote 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
    Last edited by Bryan Hessey; 09-16-2006 at 09:26 AM.

  3. #3
    Registered User
    Join Date
    09-16-2006
    Posts
    3

    Red face

    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

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    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.

    ---

    Quote 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
    Last edited by Bryan Hessey; 09-17-2006 at 01:08 AM.

  5. #5
    Registered User
    Join Date
    09-16-2006
    Posts
    3
    Much thanks Bryan!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1