Importing data from another workbook

M

Misterxpro

I have an Excel Project to do as part of my work, here is th
situation:

I need to make a commission calculator known in the workplace a
"Doshometer" I need to spreadsheet to be able to pull information fro
another spreadsheet for example:

If i enter a product code i want the spreadsheet to automatically pul
the Discription, price and commission from another spreadsheet, so b
just entering the product code the main spreadsheet will automaticall
fill in the rest of the information.

Sounds complicated i know but i only have six week to produce a workin
Doshometer. Can you excel guru`s help me out of a hole
 
T

Tom Ogilvy

You should be able to do this using the Vlookup formula or a combination of
Index and Match. You would use Vlookup if the left most column in the
source table contains the product code (or it is at least to the left of the
information you want to retrieve).

Use of Vlookup is explained pretty well in Excel help.
 
D

dmang

You could use the Find object, and a little code to set up the Find.
(assuming a description is in column 5 on the worksheet)

Sub FindItem()

Dim strSheet As String
Dim iRow As Integer

strSheet = ""
iRow = -1

If FindInSheets("ABC", strSheet, iRow) = True Then
strDesc = Worksheets(strSheet).Cells(iRow, 5)
'...
'...
End If

End Sub


Function FindInSheets(strText, _
strSheet, _
iFoundRow) As Boolean
'strText - text to find
'strSheet - sheet name on return
'iFoundRow - row on sheet where found

Dim sht As Worksheet
Dim c As Object
Dim irow As Integer
Dim icol As Integer
FindInSheets = flase
For Each sht In Worksheets
sht.Activate
With ActiveSheet.Cells
Set c = .Find(strText, LookIn:=xlValues)
If Not c Is Nothing Then
iFoundRow = c.Row
strSheet = sht.Name
FindInSheets = True
Exit Function
End If
End With
Next sht

End Functio
 
Top