Expert: Jan Karel Pieterse - 7/13/2009

Question
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

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.

---------- FOLLOW-UP ----------

QUESTION: Hi Jan,

Thanks very much for the formulas, they work great.
The reason I asked if it's possible to do a macro is because I want to prevent any sort of mistakes when printing worksheet2(table).

For ex:
If I have 150 rows of formulas entered in worksheet2(table), when I input an item which only have 3 or 4 rows of data, I will have to set the print area in order to avoid printing extra blank pages.  But if an item has more than 150 rows to be listed in worksheet2, I might also be missing some data because I did not enter enough formulas in worksheet2.

Is there any way I could prevent this?

Thanks again for your help.

K


ANSWER: Hi Karsten,

Is there any reason why you would not use Autofilter on the original list and print the filtered list?

---------- FOLLOW-UP ----------

QUESTION: Hi Jan,

Thanks for your quick response.

I apologize but perhaps I should better clarify my situation (and make better sample screenshots, my apology again).  The reason I did not use autofilter is because:

The table at worksheet2 is the specific format which I need to show to my boss.  At the top of worksheet2 at row 1, there are actually another 2 display fields at cell(B1) and cell(D1).  Those two cells are stored with vlookup formulas where when I type an item no. at cell(G1), they will display the item name at (B1) and packing at (D1).  Those 2 data are pulled from a separate data list at worksheet3.

In worksheet1(data list), the one which I work with.  In the actual worksheet, there are actually several more columns of data in between and after column H from the sample screenshot that you see.  Those columns are only for my data entry and not needed to be displayed in the sheet2(table).  If I were to use autofilter at my data list, I would assume every time I make a printout to show my boss (which is very frequent), I will have to type in the Item Name, Packing and Number, and then hiding all unnecessary columns, then adjusting the layouts to match with that of sheet2 before I print.  And once I am finished with printing I will have to reset everything back to my format, which I hope I can avoid this process.

Thanks very much for your time again.

K

Answer
Hi Karsten,

No problem.

- Add this formula to cell J1 on the "print" sheet:

=COUNTIF(Sheet1!$A$1:$A$55,$G$1)

- rightclick the print sheet's tab and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Me.Range("G1")) Is Nothing Then Exit Sub
   Me.Range("A3:I3").Copy Me.Range("A3:H3").Resize(Me.Range("J1").Value)
   Me.PageSetup.PrintArea = Me.Range("A1:H1").Resize(Me.Range("J1").Value + 2).Address
End Sub