Populating values in cells based on lookup from other cell.

[Khozema Sherwala]

Hi,

I would like to know the approach to automate one of this requirement on
excel sheet.

1. I have a workbook with sheet1 having my master data column A (ID), Column
B (FirstName), Column C (LastName) with around 500 records.
2. I will be using blank sheet2 of same workbook to enter ID's in column A
using a barcode scanner.
3. Based on the value of sheet2 column A i.e. (ID), I would like to run a
lookup of ID value in sheet1 and populate sheet2 column B and column C with
corresponding values from sheet1 for respective ID. i.e. respective First
Name and Last Name.
4. And then punch a timestamp in sheet2 columnD of same record.

I would like to know what's the best way to achieve this automation using
formula or programming any macro on key press events of sheet2 column A
values.

Thanks,
Khozema

2. I need to populate data in column B and C in my sheet 1 based on the
value in colu

 

[JBeaucaire]

VBA is fine, but you can get the column B and C values to appear using simple
INDEX/MATCH formulas.

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based
on some other restrictions about the layout of the table you're searching.
INDEX/MATCH has none of those restrictions.

Here's a sample sheet showing exactly what you're looking to do.
https://sites.google.com/a/madrocke...iles/IndexMatch-Simple.xls?attredirects=0&d=1

For #2, you can use a worksheet_change event to watch column A and put a
time stamp in column D when Col A gets a value.

Right-click on the sheet tab for Sheet2 and select VIEW CODE.

Paste in this sheet macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Range("D" & Target.Row) = Now
End Sub


Close the editor and save your sheet. Now placing a value in column A will
get you an instant time stamp in D.

 

[Khozema Sherwala]

Thanks JBeaucaire: Your pointer and help was really appreciated. Thanks again
for the sample sheet. Appreciate it.

 

[Khozema Sherwala]

Hi JBeaucaire,

I implemented the first part and its working fine. I also implemented the
suggested second point, but somehow, i do not get any results. I mean value
in D is just blank. Did some debug on macro but finally nothing worked out.
Any suggestion ? Thanks!

Regards,
Khozema

 

[JBeaucaire]

You can shoot your sheet to me at

JBeau
AT
madrocketscientist
DOT
com

And I'll take a look for you. That macro must be IN the sheet module for it
to work. If you placed it in a regular module...no dice.

-Jerry