Manually overwrite cell value without deletin

September 30, 2010 at 03:06:37
Specs: Windows XP
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

See More: Manually overwrite cell value without deletin

Report •

September 30, 2010 at 08:53:25
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 once and 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.


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

Report •
Related Solutions

Ask Question