Override v lookup

Can I do this without macros.

I would think not. A cell can have a formula, in this case VLOOKUP, or a value, but not both. In your example, the value would be obtained from a Data Validation drop down box. It would be just the same as typing a value directly into the cell, overwriting the formula.

Regards

It's possible but it would need some setting up.

This is one way to do it; there could be other and better ways.

In the example code that follows, I have used a Workbook_SheetActivate event monitor. This needs to go in the Workbook Class Module, not an ordinary module. It monitors sheet activation(s)... all sheet activations. There is no checking in this code but you might want to consider testing which sheet is activated. This could be done by checking part of the sheet name or testing specific cells on the active sheet, for example, headers.

The code checks a "marker" cell (A1) for the value "new" (without quotes). If it finds it, it resets the marker to nothing , clears the search range and re-inputs the formulae and validation. You didn't specify what the validation list is and you didn't provide an example workbook, so I've just made something up to test the principle.

So, if you wanted to reset any sheet (with this structure) you could put "new" (without the quotes) in cell A1, activate another sheet and then re-activate the original sheet.

You could also record a macro which copies the sheet and puts "new" in cell A1 on the copy; I haven't done that as you may already have some code to copy the sheet(s).

Please Login or Register  to view this content.

If cell A1 is in use, you'll need to pick another suitable cell and modify the code appropriately. I've used With ... End With so you only need to change the cell address once.

Regards