Excel/macro table

Advertisement


Question
sample
sample  
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

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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


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, ...

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