Excel/macro table
Expert: Jan Karel Pieterse - 7/10/2009
Question
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
AnswerHi 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