Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals and links (Excel 2003)

    hi there
    firstly i need to link sheet 1 to sheet 2 for cost and produce code.
    secondly i need to find a nested IF fuction find totals on sheet 1 based on the data, based on the product code, and quatity in sheet 1 and the product code and price on sheet 2 using a dymatic link?
    i think i have explained that right? i have included the sheet so you can have a better idea of what im talking about. thanks kitty
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals and links (Excel 2003)

    would a vlookup table work better if so how do i make it work?

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Totals and links (Excel 2003)

    Does this formula in F2 do what you want?
    <code>=VLOOKUP(A2,Sheet2!price_list,2,FALSE) * E2</code>

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Totals and links (Excel 2003)

    As you are having difficulty with the formula check for local/global named ranges. The file you attached contains a local named range. Does your original file use a global named range instead?

  5. #5
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals and links (Excel 2003)

    hi there not quite sure about your answer, as ive never heard of local/global, im assumming local is the page your working on and global is another sheet in the work book, i have attacted the sheet again.
    Attached Files Attached Files

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

    Re: Totals and links (Excel 2003)

    The formula
    <code>
    =VLOOKUP(A2,Sheet2!price_list,2,FALSE) * E2
    </code>
    doesn't work in your new workbook since the sheet that was named Sheet2 in the original version is now named Price-list. You must adjust the formula accordingly. Moreover, you must put single quotes (apostrophes) around the sheet name, because otherwise Excel thinks that the - in the name means a subtraction ("Price minus list"). Also, you have inserted some extra rows above the table, so the first formula is now in row 4 instead of row 2. So it becomes
    <code>
    =VLOOKUP(A4,'Price-List'!price_list,2,FALSE) * E4</code>

  7. #7
    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: Totals and links (Excel 2003)

    In F4:
    =VLOOKUP(A4,'Price-List'!price_list,2,FALSE) * E4

    Your name "Price_List" is no longer on Sheet2 but on a sheet called "Price-List". If you delete the local name "Price_List" in the sheet and recreate it as a global name then you can just use (without the sheet name):

    =VLOOKUP(A4,Price_List,2,FALSE) * E4

    To delete the local name and create a global one:
    Select the "Price-list" sheet
    Insert - name - define
    Select the name "Price_List"
    <delete>
    Enter:
    Price_List
    in the "Names in workbook" box
    In the "refers to" box enter:
    =$A$3:$B$20
    <ok>

    Steve

  8. #8
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals and links (Excel 2003)

    Thank you all sorted now, funny how silly little mistakes totally throw 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
  •