Consulting

Results 1 to 2 of 2

Thread: Sheet Reference based on a Cell Value???

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location

    Sheet Reference based on a Cell Value???

    Hello all,

    I was wondering if there is a way to use a value in a cell (that cell value would exactly match the name of one of the worksheets) as a sheet reference in a formula?

    ex. cell B4 = "Widget"
    one of the sheets would also be named "Widget"
    I want to use the cell value to reference the sheet name.

    a formula would read...

    =vlookup($A4,'Widget'!$A$2:$Z$10000,match(P$1,'Widget'!$A$1:$Z$1,0),0)

    in this formula I would want it to use the cell value in B4 (Widget) in place of the sheet reference in the formula ('Widget'!)

    Thank you all!
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,756
    try:
    =VLOOKUP($A4,INDIRECT($B4 & "!$A$2:$Z$10000"),MATCH(P$1,INDIRECT($B4 & "!$A$1:$Z$1"),0),0)
    but it your sheet names have got spaces in it needs to be:
    =VLOOKUP($A4,INDIRECT("'" & $B4 & "'!$A$2:$Z$10000"),MATCH(P$1,INDIRECT("'" & $B4 & "'!$A$1:$Z$1"),0),0)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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