CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Andrew Perry
Andrew Perry

Reputation: 765

Excel cell showing value from formula while still allowing manual entry

I have a column of cells populated through a VLOOKUP. However, I've now been asked to make those cells allow for a manual override, while still showing the VLOOKUP value if there's no override.

Unfortunately there's a requirement to have the override entered in the same cell - otherwise I'd just add a couple of helper columns and it'd be trivial.

Is there another way to let a cell show a formula-based value, accept an override and restore the formula if there's no manual value?

Upvotes: 0

Views: 3583

Answers (2)

J Brazier
J Brazier

Reputation: 884

As mentioned, there'd no way to make it automatically restore the formula; you have to do it using VBA; I recommend a Command Button with a script that resets the cell.

If you want to have minimal reliance on VBA, and not lose the formula, you could set it so that the result is actually calculated in a cell hidden elsewhere, and the cell that the user inputs to just points there automatically.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

The usual practice is to use an extra cell. For example, put the Vlookup() formula in cell B1 and the override value in cell C1,

Then in A1 enter:

=IF(C1="",B1,C1)

This allows A1 to display either the Vlookup() or the override (if it has been entered)

Upvotes: 1

Related Questions