Fill cell value based on value from another sheet

**tcy1227** · 07-09-2012, 12:47 PM

Hi,

I am having difficulty with the following and any help is greatly appreciated;

I am trying to fill a cell value based on a value in the column to it's left, and using a value from a separate sheet.

For example: Column B is Client Name, Column C is Client Number. I have a separate worksheet titled "Client No List." I want to be able to enter the client name (for example in row 5 - so B5), and have the client number automatically fill C5 with the number.

I had this working before, but someone messed with sheet and I can't figure it out again!

Here is what I had:

If Not Intersect(Target, Columns("B")) Is Nothing Then
Dim tmp As Range
Set tmp = Sheets("Client No List").Range("A2:A1500").Find(Target.Value)
If Not tmp Is Nothing Then
Target.Offset(0, 1).Value = tmp.Offset(0, 1).Value
Else
Target.Offset(0, 1).Value = "???"
End If
End If

**ben_hensel** · 07-09-2012, 12:55 PM

I think VBA is rather more solution than you need...

B5 = client name //the input
C5 = VLOOKUP(B5, Client_No_List!$A$2:$B$1500,2,FALSE)

Note this is going to be picky; your input will have to EXACTLY match the spelling, layout, name (Fred vs Freddy vs Fredrick), of the table it's looking to.

EDIT: It occurs to me I'm making some rather substantial assumptions about the layout of your spreadsheet. Post a copy (with sensitive information stripped out/ replaced with dummy info) and I can verify or correct my assumptions.

**tcy1227** · 07-09-2012, 01:32 PM

Thanks - I was wondering if I needed VBA or Vlookup would do the trick.

Here is the basic and relevant layout - I am trying to figure out how to upload from our internal server to the site. In the meantime, I saved in google docs, if you can access this: https://docs.google.com/spreadsheet/...DFpeTk1aUowVlE

**tcy1227** · 07-09-2012, 01:47 PM

Sorry - I think this may work. If not, I'm working on it...

**martindwilson** · 07-09-2012, 01:37 PM

google docs are not excel, formulas are entered differently

**oeldere** · 07-09-2012, 02:06 PM

See collumn G (yellow cells)

I worked with datavalidation in collumn C.

I worked with defined names (clientname)

Do you have questions, on this item, just ask.

**tcy1227** · 07-09-2012, 04:32 PM

Thanks. That should work. Is there any way to write this so that it recognizes an incomplete name? For example, if I have a client named Jane Doe, cane I just type Jane in sheet 1, column C and the program will recognize that as Jane Doe (assuming there are no other Janes). I believe the VBA I had could do this...