Excel: take horizontal data from table, organize vertically in a different sheet, ignoring blanks

microsoft-excel-2010

I have a table of vendor contact info organized like:

vendorname | address | city | state | zip | phone | fax | contactperson | phone | email

…and I want to select vendorname from a dropdown menu on a different sheet (got that part handled) which would then populate the contact info for that vendor in the cells below.

To make it trickier, I want it to ignore any blank columns from the table. For example, say there is no fax number for an entry in the table. I don't want it to put an empty cell between 'phone' and 'contactperson.'

Can this be accomplished just using formulas?

Best Answer

You probably want to use the VLOOKUP() function. This would be an example:

=VLOOKUP($B$3, Table1,2,FALSE)

Where $B$3 is the cell with the dropdown and Table1 is the table with contact data. Put this formula in the first cell to be filled in when an item is selected from the dropdown. In the other cells to be filled in, you simply need to increment the third parameter (the column index parameter; eg 2 in the sample above).

AFAIK, there is no way to skip blank columns with this method. You'll probably need VBA for that.

Related Solutions

Excel – How to read data from Excel Sheet using Excel VBA 2010

Here's an example of how to handle a text file directly

Private Sub CommandButton1_Click()
    Dim shSrc As Worksheet
    Dim rSrc As Range
    Dim dat As Variant
    Dim FileNumber As Integer
    Dim rw As Long, cl As Long

    Set shSrc = Worksheets(Me.Cells(2, 1).Value)
    dat = shSrc.Range(Me.Cells(2, 3).Value & ":" & Me.Cells(2, 4).Value).Value

    FileNumber = FreeFile
    Open Me.Cells(2, 2).Value For Output As #FileNumber
    For rw = 1 To UBound(dat, 1)
    For cl = 1 To UBound(dat, 2)
        Print #FileNumber, dat(rw, cl)
    Next cl, rw
    Close #FileNumber
End Sub

Note:

Assumes an ActiveX button
You will need to add error handling
You don't say what layout you want in the text file. This justs write one cell per line.

Best Answer

Related Solutions

Excel – How to read data from Excel Sheet using Excel VBA 2010

Related Question