AutoFill From One Sheet to Another

[montyfern]

Greetings,

My client wants to do this: My goal is to use the directory in the second tab to auto fill the row based on the last name entered. For example, if I type in Smith, the row will autofill with the first name, location, branch, and supervisor. I would then have to manually fill in the device type, decal, etc.

I've looked at other posts and forums and so far am empty handed. Is this an Index, Match, VBA, or? I tried to write an Index and Match to no avail:
=INDEX(XYZ DIrectory!A$5:A$500,MATCH($A5,All XYZ Property!$A$4:$A$500,0))

Thanks!

 

[mumps]

Can you post a sample of what your data looks like. Look at Section B at this link for instructions: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[montyfern]

Last Name	First Name	Branch	Supervisor	Office Phone Number	Cell/iPhone #	Email Address	Office/Workstation Location
Ohio	Akron	SPEB	Smith, Joan	703.525.6666		ak.oh@aol.com	FL/1000
Arlington	Virginia	SPEB	Smith, Joan		703-979-8885	arling.va@aol.com	Off-Site

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

Greetings,

My client wants to do this: "goal is to use the directory in the second tab to auto fill the row based on the last name entered. For example, if I type in Smith, the row will autofill with the first name, location, branch, and supervisor. I would then have to manually fill in the device type, decal, etc."

I've looked at other posts and forums and so far am empty handed. Is this an Index, Match, VBA, or? I tried to write an Index and Match to no avail:
=INDEX(XYZ DIrectory!A$5:A$500,MATCH($A5,All XYZ Property!$A$4:$A$500,0))

Thanks!

Here you go, MUMPS, and apologies. I had to "dummify" the data. Thanks very much!

SHEET1 Last Name

First Name

Location

Branch

upervisor

Device Type

Decal #

Manufacturer

Model

Purchase Date

Surplused?

Notes

Replacement Estimate

Akron

Ohio

FL/100

OHO

Smith, Joan

Printer

00011100

HP

Q5401A

11/15/2005

No

Akron

Ohio

FL/100

SPEB

Schwetz, Tara

Monitor

01729185

Dell

2408WFP

5/29/2008

No

Akron

Ohio

FL/102

SPEB

Schwetz, Tara

Laptop

01984244

HP

ZBook15

9/24/2015

No

SHEET2 Akron

Ohio

FL/103

SPEB

Schwetz, Tara

Computer CPU

02042515

HP

Z230

5/25/2016

No

Last Name First Name Branch Supervisor Office Phone Number Cell/iPhone # Email Address Office/Workstation Location Ohio Akron SPEB Smith, Joan 703.525.6666 ak.oh@aol.com FL/1000 Arlington Virginia SPEB Smith, Joan 703-979-8885 arling.va@aol.com Off-Site <tbody> </tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[mumps]

My apologies. I know you have gone through a lot of trouble posting your data but the way the data is displayed is confusing. You say: "if I type in Smith" but I don't see "Smith" in the Last Name column. I see names of cities or states. Also, what happens if there are more than one "Smith"? Could I ask you to upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. For example, where do you type in the last name. If the workbook contains confidential information, you could replace it with generic data. Working on your actual file will make it easier to understand how your data is organised and test any possible solution.

 

[montyfern]

My apologies. I know you have gone through a lot of trouble posting your data but the way the data is displayed is confusing. You say: "if I type in Smith" but I don't see "Smith" in the Last Name column. I see names of cities or states. Also, what happens if there are more than one "Smith"? Could I ask you to upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. For example, where do you type in the last name. If the workbook contains confidential information, you could replace it with generic data. Working on your actual file will make it easier to understand how your data is organised and test any possible solution.

https://www.dropbox.com/s/8gonvxw8glb77j0/MOCK_AUTOFILL_EXAMPLE.xlsx?dl=0

No my apologies, mumps. We had a gov't shutdown & I had to leave tres vite. My only detailed explanation is auto-filling data. There is no specific cell reference. All I need please is when I enter a last name in Cell A1 or A20 or whatever, then an auto-fill function knows to add the First Name, Location, Branch, and Supervisor. I would have to manually add device type, decal, etc. Does this make sense? Is it a macro?
Thanks MUCH!

 

[montyfern]

mumps, my names are (last name) OHIO (first name) AKRON. If I start typing Ohio then auto-fill knows to add Akron, my Location, branch & supervisor. Hope that clarifies.

 

[mumps]

Hopefully, I have understood correctly. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a last name in column A and exit the cell. I noticed that on Sheet2 you had the last and first name columns reversed. Make sure that the last names in Sheet2 are in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundName As Range
    Set foundName = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundName Is Nothing Then
        Target.Offset(0, 1) = foundName.Offset(0, 1)
        Target.Offset(0, 2) = foundName.Offset(0, 7)
        Target.Offset(0, 3) = foundName.Offset(0, 2)
        Target.Offset(0, 4) = foundName.Offset(0, 3)
    End If
    Application.ScreenUpdating = False
End Sub

 

[montyfern]

Hopefully, I have understood correctly. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a last name in column A and exit the cell. I noticed that on Sheet2 you had the last and first name columns reversed. Make sure that the last names in Sheet2 are in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundName As Range
    Set foundName = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundName Is Nothing Then
        Target.Offset(0, 1) = foundName.Offset(0, 1)
        Target.Offset(0, 2) = foundName.Offset(0, 7)
        Target.Offset(0, 3) = foundName.Offset(0, 2)
        Target.Offset(0, 4) = foundName.Offset(0, 3)
    End If
    Application.ScreenUpdating = False
End Sub

Dear Mumps, PERFECTO! I had to rename the sheet to her desired name so I edited the macro. When I insert a line the macro errors out as it doesn't understand what cell it's referencing but as long as it's in column A I can just hit End and it works brilliantly.

MANY MANY thanks. I don't like VBA but teach Excel Headed to Power Pivot and Power Query....

 

[mumps]

You are very welcome. Try this version. It should eliminate the error.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundName As Range
    Set foundName = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundName Is Nothing Then
        On Error GoTo errHandler
        Target.Offset(0, 1) = foundName.Offset(0, 1)
        Target.Offset(0, 2) = foundName.Offset(0, 7)
        Target.Offset(0, 3) = foundName.Offset(0, 2)
        Target.Offset(0, 4) = foundName.Offset(0, 3)
    End If
errHandler:
    Application.ScreenUpdating = False
End Sub

 

[montyfern]

It sure did, thanks a ton!