1. ## 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

2. ## Re: Totals and links (Excel 2003)

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

3. ## 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. ## 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. ## 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.

6. ## 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. ## 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. ## 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
•