ITEM UNIT PRICE QTY
TOTAL



When an Item is selected It will fill in a Unit Price. User will enter
Quanity needed. Total will be calculated. If an Item is deleted, I'd like
for the Qty to revert to 0.00. Is there a simple way?

Hi Barry,

This might be what you are wanting....

Sheet2 column A heading = Item. Leave cell A2 blank then with A3 down
to An+2, where n = number of Items, enter the Item names.
Make this column a Dynamic Named Range with the name "Items" by
going...

Sheet1 tab|Insert|Name|Define...|type "Items" (w/o quotes) in the
"Names in workbook:" box at the top of the "Define Name" dialog then
click in the "Refers to:" box at the bottom of the same dialog and type
in the following...

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A))

Click the Add button followed by the OK button to complete the Naming
process.

Sheet2 column B heading = Unit Price. Cells B2 (B2 = 0) down contain
the corresponding column A Item's unit price and are formatted
Currency.
Make this table of Items and Unit Prices a Dynamic Named Range with the
name "Table" by going...

Stay on Sheet2|Insert|Name|Define...|type "Table" (w/o quotes) in the
"Names in workbook:" box at the top of the "Define Name" dialog, then
click in the "Refers to:" box at the bottom of the same dialog and type
in the following...

=OFFSET(Sheet2!$A$1:$B$1,1,0,COUNTA(Sheet2!$A:$A))

Click the Add button followed by the OK button to complete the Naming
process.

Sheet1 A1 heading = Item
Sheet1 B1 heading = Quantity
Sheet1 C1 heading = Unit Price
Sheet1 D1 heading = Total

Select Sheet1 A2 down to Am+1, where m is the maximum number of
selected Items you want shown in Sheet1 column A then add Data
Validation so that the user can select items from a drop down list of
the Items listed on Sheet2. To do this go...

Data|Validation...|On the Settings tab click on the drop down arrow on
the right of the "Allow:" box and select "List"| click in the "Source:"
box and type "=Items" (w/o quotes)|OK.

Enter the following formula into Sheet1 C2, then fill it down to
Cm+1...

=IF(ISERROR(VLOOKUP(A2,Table,2,FALSE)),"",VLOOKUP( A2,Table,2,FALSE))

Enter the following formula into Sheet1 D2, then fill it down to
Dm+1...

=IF(C2="","",B2*C2)

So that 0.00 appears in the Quantity column when the user selects the
top blank item in the column A drop down list, the following code needs
to be pasted into Sheet1's code module...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 1 Then
If Target.Value = "" Then
Target.Offset(0, 1).Value = 0
End If
End If
End Sub

To get the code in place...

1. Copy it
2. Right click the Sheet1 tab then select "View Code". This opens the
Visual Basic Editor at Sheet1's code module.
3. Paste the code into the module.
4. Press Alt + F11 to get back to Excel.
5. For the code to work the workbook's Security needs to be set on
Medium and "Enable Macros" must be clicked on the "Security Warning"
dialog that will appear when the workbook is opened. If the Security
setting is higher than Medium then go Tools|Macro|Security... click the
Medium option then OK|Save|Close|Open and click "Enable Macros" on the
"Security Warning" dialog.

Ken Johnson

There is a way. Whether it's simple is a matter for you to decide!

First, make a list of your items and their unit price (probably best on a
separate sheet)

Then, under your item header, create a selectable list using Data, Validation.
The settings should be Allow List, Source =your list (the help file is quite
good at explaining this - Create a drop-down list from a range of cells)

Next, under Unit price you'll need a formula to lookup your selected Item
and fill in the price. Something like
=IF(A2="","",LOOKUP(A2,Sheet2!$A$1:$A$100,Sheet2!$ B$1:$B$100))

This will take care of your requirement of deleting an item, the value is
blanked out.

Finally a formula to calculate the Total =IF(A2="","",B2*C2) again checking
the item is present to avoid an error in the calculation.

Give it a try, see if it meets your requirements

"Barry" wrote:

ITEM UNIT PRICE QTY
TOTAL



When an Item is selected It will fill in a Unit Price. User will enter
Quanity needed. Total will be calculated. If an Item is deleted, I'd like
for the Qty to revert to 0.00. Is there a simple way?