Search worksheet 3 and return the information in worksheet 1 in Excel

[Aspesim]

Hi Everyone

Wonder if you can help me once again.

In Worksheet 3 Lets say I have 3 columns, Their headings are Asset Tag, Serial Number and Mac Address under each I have 150 entries.

When In worksheet 1 and I enter the Asset Tag number into column H, how do I get excel to search all the Asset Tag numbers in worksheet 3 and automatically enter both the corresponding serial number into column J and the Mac Address into column L in worksheet 1?

Regards
Aspesim

 

[SkipVought]

Hi,

Just use a lookup spreadsheet function. No need for VBA, this is an Excel built-in feature. Forum68 if you need specific help with spreadsheet functions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Aspesim]

Hi Skip and Eveyone else for that matter.

I've had a look at the link and a few threads within but I'm none the wiser...

When I put the asset tag number into column H do I always have to type =Lookup **(asset tag number)*** etc. I just wanted to type the asset tag number in column H and cells J and L automatically update.

My problem is I'm better at destructing and learning how its all linked together and make relevent changes to suit, but I'm no good at setting something like this up (still a newbie after all less then a weeks basic knowledge)

There seems to be many formulas i.e Vlookup, Lookup MS Query etc, not that I know any of them.

I guess its one step at a time.

Regards
Aspesim.

 

[xlbo]

If you have the VLOOKUP formula set up - all you need to do is copy it into the next row

I would suggest doing it this way 1st before trying to code it up as that would require an understanding of worksheet events and using the FIND method in VBA. However, as an example, this kind of structure would be what you require:

Private Sub Worksheet_Change(ByVal Target As Range)
dim fCell as range
dim fSht as worksheet

If target.column <> 8 then exit sub

set fSht = sheets("Sheet3")

set fCell = fSht.columns(1).find(target.value,lookin:=xlvalues, lookat:=xlwhole)

if not fcell is nothing then

   target.offset(0,2).value = fCell.offset(0,1).value
   target.offset(0,3).value = fCell.offset(0,2).value

end if

set fSht = nothing

End Sub

Typed untested - assumes asset numbers in Col A of sheet 3 and the other data in adjacent columns. This would go in the WORKSHEET module for sheet 1...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Aspesim]

Hi Geoff

That worked just fine.

I've learned quite a lot from your code, changing the figures and seeing what changes are made in my (test) spreadsheet

However within my (real) spreadsheet each User will have 1 or 2 workstations and 2 monitors all of which are asset tagged so how can I Target column 8(H), 15(O), 21(U) and 26(Z). (All columns will be used for asset tag information and will search the same range of data).

I have tried If target.column <> 8 then exit sub
If target.column <> 15 then exit sub

I have also tried If Target.Column <> 8 Then 15 Then Exit Sub

and If Target.Column <> 8 goto 15 Then Exit Sub

but it didn't work and even stopped the original from working as well?

Any help as always will be greatly appreciative.

Regards
Aspesim

 

[PHV]

Select Case Target.Column
Case 8, 15, 21, 26
Case Else
Exit Sub
End Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aspesim]

Hi PH

Tried and tested. )

Excellent!!

Thanks guys