links in excel

  • Thread starter Thread starter TA
  • Start date Start date
T

TA

I am trying to link 2 excel sheet parts list to a
quotation sheet.
I have a parts list in excel with part numbers
description and pricing. I am trying to link it with a
quotation excel sheet. so when I type the part number in
the part number colum it brings up all the details.
description pricing etc:

Please: any help with this would be good
 
I am trying to link 2 excel sheet parts list to a
quotation sheet.
I have a parts list in excel with part numbers
description and pricing. I am trying to link it with a
quotation excel sheet. so when I type the part number in
the part number colum it brings up all the details.
description pricing etc:
Click to expand...

It's difficult to provide you with a definite solution without knowing
how your workbook is structured, but this should give you an idea or
two. Let's say that you have three columns on both your quotation
sheet and your price list; Part No (column A), Description (B) and
Pricing (C). Your parts list sheet could be called PartSheet.

Right click on the sheet tab of your Quotations sheet and select View
Code. Now paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim vnt As Variant

'This is the range that contains the part numbers.
Dim rng_PartNo As Range

Set rng_PartNo = Worksheets("PartSheet").Range("A:D")

'Target is the cell that changed.
'Only run this procedure if the changed cell is in
'column A, below the heading.
If Target.Row > 1 And Target.Column = 1 Then

vnt = False

'The Vlookup command will generate an error
'if the part no doesn't exist.
On Error Resume Next

'Use the worksheet Lookup function to see if the part
'no is there.
vnt = Application.WorksheetFunction.VLookup _
(Target.Value, rng_PartNo, 1, False)

'If it's not, put error values in.
If vnt = False Then

Target.Offset(0, 1) = "No such part."

Target.Offset(0, 2) = ""

Else

'Look up the description and
'costs.
Target.Offset(0, 1) = _
Application.WorksheetFunction.VLookup _
(Target.Value, rng_PartNo, 2, False)


Target.Offset(0, 2) = _
Application.WorksheetFunction.VLookup _
(Target.Value, rng_PartNo, 3, False)


End If

End If

End Sub

Refine it however you need to.
 
Hank,
I have the following
Parts list
A colum has part number
B colum has description
C colum has u/m
D colum has list price
E colum has discount
f colum has margins
G colum has cost price

The quote sheet has
C colum has part number
D colum has description
E colum has source
F colum has cost

Can you please help with this one
 
Another way to do it maybe to just use the =vlookup() formula in adjacent cells
(eschewing the VBA routine). (eschewing, hehe).

Since your Parts list table is nicely laid out, you could a formula like this to
return the values:

Say C1 of the quote sheet is the start of your data.

To retrieve the description, put this formula in D1:

=vlookup(c1,parts!$a$1:$G$9999,2,false)

(I used Parts as the worksheet name for the parts list.)

That 2 tells excel to bring back the 2nd column from the lookup range.

To retrieve the cost, you'd use a formula like this in F1:

=vlookup(c1,parts!$a$1:$G$9999,7,false)

And just multiply that by the quantity to get the extended cost. (I didn't see
a quantity column???).

But if the part number on the quote list doesn't match a part number in the
parts list, you'll get an error back.

You can hide it with something like:

=if(iserror(vlookup(c1,parts!$a$1:$G$9999,7,false)),"missing",
vlookup(c1,parts!$a$1:$G$9999,7,false))
 
Back
Top