Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula for Extracting values (Excel XP)

    I have a workbook with a lot of recipe costing in. I use the ASAP utilities to create an index page. I want to use the worksheet name in the vlookup formula in order to show the value of a cost calculation per recipe. Each recipe is on a differant worksheet. The referance cell is not row specific but column specific, so I was thinking of using the description of "Ingredient cost per unit :" in a vlookup formula Refer attached file

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula for Extracting values (Excel XP)

    You can use the INDIRECT function: in B2, enter the formula

    =INDIRECT(A2&"!G36")

    and fill down to B4. In B2, the expression A2&"!G36" evaluates to "Bread!G36", and the INDIRECT function looks up the value of Bread!G36.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula for Extracting values (Excel XP)

    I'm still battling. I can't use redirect because the cellreferance will differ for each differant recipe. Can I use =VLOOKUP("Ingredient cost per unit :",A2&"!F:G",2,0). How do I change the syntax to work. A2 will be the sheet names as per the index

    Tia

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula for Extracting values (Excel XP)

    <P ID="edit" class=small>(Edited by sdckapr on 04-Jul-06 07:55. Forgot to lock the row...)</P>You could use:
    =VLOOKUP(B$1,INDIRECT(A2&"!f:g"),2,0)

    But the first entry (in row 27) would have to have a different name than the later 1. The vlookup will find the first one. Or you will have to set the range to do the lookup to exclude those initial entries

    Steve

Posting Permissions

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