probs with devising quote system from master list of products

[bloors]

Dear all,

I would appreciate any help...

I am working with a master price list of products (ColumnA) and uni
prices (ColumnB) in a worksheet from which I would like to selec
products with prices and compile into a customer specific quote in
second worksheet...ideally I'd like to place a marker "X" in Column
cells adjacent to the items I want adding to the quote, thus allowin
me to review the selected items and then send all "selected" item
(those with Xs adjacent to them) over into a second worksheet thereb
compiling a quote...

Worksheet1 - Product List

Column A Column B Column C

Portable PC1 £650 X
Portable PC2 £750
Printer £ 60 X
MS Office (Ed) £100 X


Worksheet2 - Customer Quote

Column A Column B

Portable PC1 £650
Printer £ 60
MS Office (Ed) £100

Total £810


...I assume I need to use the IF function possibly in conjunction wit
an array ..but otherwise I'm pretty clueless

...a more ambitious wish is to send the selected products/prices ove
into a Word table but I'm guessing I'd need some VBA code for this...

Any suggestions very gratefully received!

Regards

Simon Bloo

 

[Paul Falla]

Dear Bloors

This problem sounds like it needs a VLookup Solution.

Design your quote sheet, and in the cell where you want
the unit value of the item to appear enter the formula =IF
(B4 ="","",VLOOKUP(B4,Sheet1!$A$2:$B$5,2,FALSE)) Where B4
is the name of the item you have typed in on your quote
sheet (Eg Printer), Sheet1!$A$2:$B$5 is the absolute range
of the catalogue list of items, 2 is the column number
containing the unit values, and false tells Excel to look
for an exact match only. The If part of the formula tells
excel to look at the cell B4, and if it is blank, then to
do nothing, this will enable you to copy the formula down
through as many cells as you like with out N/A appearing
if you havenot typed in a description.

I hope this helps

Kind regards

Paul