how do i link cells so that when typing in an item, the price app.

[Guest]

Hi there,

I'm trying to create a quotation template which enables me to enter in an
item in one cell and its corresponding price appear in the next cell? Is this
possible?

I have created a spreadsheet of Items and prices (of which there are going
to be over 1000) as a refernce point but am unsure how to link these for
automatic entry into the quotation.

Anyone know how to do this?

Cheers

julie

 

[Biff]

Hi Julie!

You need to build a table of the items and prices. For
example, on sheet2 in column A you list the items and in
column B you enter the corresponding price. Assume that
table is in the range A1:B1000.

Now, on sheet1 cell A1 is where you enter the item and B1
is where you want the corresponding price. So in B1 enter
this formula:

=VLOOKUP(A1,Sheet2A1:B1000,2,0)

Now, some things to consider. If the item is not found in
the table you will get a return of #N/A. Or, if cell A1 is
empty you will also get a return of #N/A. You can suppress
that by using this formula. It will leave cell B1 blank:

=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
(A1,Sheet2A1:B1000,2,0))

Biff

 

[Biff]

Looks like I forgot something!

The reference to sheet2 should be: Sheet2!A1:B1000

Biff

 

[Mike A]

The address of the range needs to be absolute, also:

The reference to sheet2 should be: Sheet2!$A$1:$B$1000

 

[Biff]

The address of the range needs to be absolute

Not necessarily. Only if the lookup formula is to be
copied to other cells and the OP didn't mention that would
be the case.

Biff

 

[Mike A]

It is a good practice. Next week, someone will insert a row or column
above or to the left of the cell, and there goes your lookup function.

I like to make my products user-proof!!


Mike Argy
Custom Office solutions and
Windows/UNIX applications