Excel Database Function

[Leo Fredette]

Not sure where to post this..We have an export from an external database
into an Excel spreadsheet that lists our customer lists..ie: Code is three
letters and the usual demographics (address info)..We wanted to create an
app where you would doubleclick on an icon and it would prompt you for the
three letter code in Excel and then print a label to an attached DYMO label
printer of the demographic for that code. I have three users who have Excel
and not Access or I would have created it there..

Any pointers on whether this exists for Word or Excel or can it be done?

Thanks..

Leo

 

[Earl Kiosterud]

Leo,

Excel doesn't have any built-in ability to lay out print information (as
with an Access report). It prints the way the sheet is laid out. You could
set up the label on another sheet, laid out the way you want it, and use
VLOOKUP functions in each label field to extract the data from the desired
record.

First, you'll need to assign a name to the column of codes. I'll presume
it's column A of sheet1, and the first record is row 2. Insert - Name -
Define:

Name: Codes
Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A$2:$A$65536),1)

This is a dynamic name. There can be no missing codes along the way, or
it'll mess up.

Now for the label sheet. Put a cell in that sheet, outside the label area
(outside the print area) (I'll use F1 for the example below), and set it up
with Data - Validation - List, and point it to the name Codes.
Unfortunately, it won't list the customer names along with the codes.

=Vlookup(F1, CodeTable, 2, FALSE)

Codetable would either be a defined range name to the entire table (not
including the heading), or a reference to it. This one would retrieve
what's in column 2 of the list, so you'd put it in the appropriate place in
the label. Etc.

For City, State Zip, you might use something like this in a single cell:

=Vlookup(F1, CodeTable, 6, FALSE) & ", " & Vlookup(F1, CodeTable, 7, FALSE)
& " " & Vlookup(F1, CodeTable, 8, FALSE)

where those fields are columns 6-8 of the table.

 

[Leo Fredette]

I will try this tomorrow as I am teaching the rest of the day..thanks for
your help..

Leo