Excel/macro table

Advertisement

Expert: Jan Karel Pieterse - 7/10/2009


Question

sample
Hi Jan,

Appreciate your help in advance.  Could you help to see if I could make a vba macro to do the following.

I have attached a sample screenshot of my worksheets.

I have a list of data in worksheet1 where Column A contains item numbers and columns (B:E) contain the item details.  Column A will have repetitive item numbers, also items and details will be updated and added continuously in worksheet1.

I want to create a macro where in sheet2, if I input an item number in for ex: cell(E1).  It will automatically search for the item number in worksheet1, and then display the matching item descriptions in worksheet2 starting from A2:E2.  The table will be constantly expanding as more repetitive items are added into worksheet1.

Thanks very much,

K

Answer
Hi Karsten,

There is no need for a macro to achieve this, you can use formulas.

I used an extra column to the right of your sheet 2 table.

Select as many cells as the length of your table spans, e.g. cells I3:I12.

ENter this formula into those cells using control+shift+enter to tell Excel it is an array formula:

=SMALL(IF($G$1=Sheet1!$A$3:$A$12,ROW(Sheet1!$A$3:$A$12),1E+99)-ROW(Sheet1!$A$2),ROW(Sheet1!$A$3:$A$12)-ROW(Sheet1!$A$2))

Note that G2 contains the Item no to look for and Sheet1!A3:A12 the item number list.

In the customer column you enter this (normal) formula:

=IF($I3<1E+99,INDEX(Sheet1!$B$3:$B$12,$I3),"")

Use similar formulas for the other columns, adjusting the address inside the INDEX function so it returns the data from the proper range of sheet1.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2009 About.com, a part of The New York Times Company. All rights reserved.