I have an invoice form in Excel which has a list box In the list box are part numbers, when i click on a part number it automaticaly inserts part number price and labour cost via a vlookup formula from sheet 2. I want to be able to change the priice or labour or both manually whithout upsetting the vlookup formula or having to go back to the ttable on sheet 2 to change the amounts. Ihope this makes sense. i am looking for a simple solution as i am a grren horn at this level

You can't enter a value in a cell that contains a formula without disturbing that formula. Think about it. Can 2 people occupy the exact same spot at the exact same time?

As a workaround, you can change the formula

onceand accomplish what you want:Let's say your Drop Down is in A1 and VLOOKUP formula looks like this:

=VLOOKUP(A1, Sheet2!$A$1:$B$8, 2, 0)

Change the formula to read something like:

=IF(B1="", VLOOKUP(A1, Sheet2!$A$1:$B$8, 2, 0), B1)

Now you can enter your "manual" data in B1 and it will show up in the same cell as the "automatic data".

If you are concerned that there might be "left over" data in B1 that might impact subsequent invoices, you could use a Worksheet_Change macro that would clear B1 whenever a Drop Down choice was made. That would ensure that the VLOOKUP always populated the cell first and the user would have to enter something in B1 to override that value.

e.g.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then _ Range("$B$1").ClearContents End Sub

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History