autowrite a cell depending on another cell's content

  • Thread starter Thread starter jose carreno
  • Start date Start date
J

jose carreno

Hello all (newbie):

I need to: read the content of a cell input, compare its value with a list
of data and populate the contiguous cell with the corresponding value, like
an autocomplete function.

wich one is the easiest method?
i thought of
writing the data on another sheet, compare the value on a worksheet
event
same, but using an array
same, but with some sort of database-list
create one line txt files, import the text of the file named like the
cell input (managed to work this one, but seems clumsy)

all of them pose dificulties to me (new to this) so someone could please
tell me which one is easier-smarter and give me some guidelines, links or
code to get started.

the number of entries would be small-medium, not more than a hundred

I have an existing worksheet change event that does this with an image,
i.e., it reads a number from a cell and retrieves a jpg archive named with
that number. So another possible solution would be to read the caption
embedded within a jpg archive, but at first glance this seems complicated.

thanks in advance to all
 
The worksheet Vlookup function can do this without using code if your lookup
data is a worksheet.

This assumes the lookup value is in the leftmost column of the data. If
not, it can be done with a combination of Index and Match worksheet
functions. If you need to do it in code, you can use these functions in
code as well.

If the table of values is small, you can use the vlookup function with an
array

=Vlookup(A1,{1,"A";2,"B";3,"C";4,"D"},2,False)

where A1 contains a number between 1 and 4 inclusive - as an example.
 
I got a neat solution, its a worksheet change event but it only works
whithin the same sheet. I inserted two Sheets("xx").Select commands that
just dont work. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vModelo As Integer
Dim vFoto As String
Dim cell As Range
Dim vnext As String

Dim aRng As Range

If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
vModelo = Target.Value
' Sheets("Hoja2").Select
Range("W1:W10").Name = "MyRange"
For Each cell In Range("MyRange")
If cell.Value = vModelo Then vnext = cell.Offset(0, 1).Value
Next cell
' Sheets("Hoja1").Select
Target.Offset(0, 1).Select
ActiveCell.Value = vnext
Set aRng = Nothing

End Sub
 
Back
Top