Question: Automatically Filling Cells by Identifying Row

[Jennifer Waterhouse]

I've made a fax sheet in excel with a number of blocks to be filled.
Company name, ph#, fax number, part #, etc.

All this information is present on another sheet when I input my order. The
first column on that sheet is my Purchase Order Number (IE TT4153). The
rest of the row contains the product description, Company name, ph#, fax
number, part #, etc...

Here is what I would like to be able to do. Input the Purchase Order Number
in a cell on the fax sheet, press enter, and all the blocks on the fax sheet
would fill from the row of that Purchase Order.

Another way to explain it. A formula in the cell for the Company Name on
the fax sheet that would read the Purchase Order Number cell, then find that
row on sheet 2 and take whatever was input in, let's say column 'H'.

Any help would be much appreciated


MIKE

 

[Patti]

Maybe try the "Lookup" function? If the purchase order
number is in cell A1, the list of all purchase orders is
on Sheet 2 column A and the list of company names is on
Sheet 2 column B, your formula would be:

=Lookup($A$1,Sheet2!A:A,Sheet2!B:B)

Then you would copy the formula down for each other item
you want to look up and change the last range (B:B) to the
corresponding columns.

 

[Arvi Laanemets]

Hi

Do you need this sheet for sending fax'es? Why don't you use MS Word's Mail
Merge with your Excel table as source? From there you can send both faxes or
e-mails directly - and you can estimate there, which rows of your table are
processed - a single order, or some selection, or the whole table.

To send faxes, your system must include a MAPI-compatible fax program.

You must have all info for every fax/mail on single row of your source
table, your table must have a header row, and it's better when your source
table is on first sheet in workbook.

In Word, select Mail Merge from Tools menu, create main document as form
letter, and select your Excel table as data source.

Design main document, inserting fields from source table into proper places.
You are free to use all designing/formatting features, available in Word
(tables, Super- and Subscript, different fonts, etc).

Save your main document for latter use. Whenever you open it later, last
used source table is opened along with. You can use various source tables
with same main document, or you can use same source table with various main
documents - when all fields (column headers) used in main document are
present in table. Order of fields in table isn't signifant.

After you created (or re-opened) the main document, select from Tools menu
again Mail Merge, and in helper window press Merge button. In this window
you can set filters to your source table, or estimate the row number(s) to
be processed. Here you also estimate the destination - new document, or
printer, or e-mail, or fax. When you select e-mail or fax as destination,
you are asked for field in source table, containing mail address or fax
number when document(s) is/are processed later

When you don't have a fax program in your computer, then you have to print
your faxes of-course - select the printer as destination when this is the
case.


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



I've made a fax sheet in excel with a number of blocks to be filled.
Company name, ph#, fax number, part #, etc.

All this information is present on another sheet when I input my order. The
first column on that sheet is my Purchase Order Number (IE TT4153). The
rest of the row contains the product description, Company name, ph#, fax
number, part #, etc...

Here is what I would like to be able to do. Input the Purchase Order Number
in a cell on the fax sheet, press enter, and all the blocks on the fax sheet
would fill from the row of that Purchase Order.

Another way to explain it. A formula in the cell for the Company Name on
the fax sheet that would read the Purchase Order Number cell, then find that
row on sheet 2 and take whatever was input in, let's say column 'H'.

Any help would be much appreciated


MIKE

 

[Jennifer Waterhouse]

That works like a charm. Thanks for the formula, Patti. I needed it Dumbed
down like that for me.
You're wonderful Patti

Thanx everyone
MIKE