Delete value but not formula

[Big Ant]

Hi,
I've written a spreadsheet to help with quoting engineering work. In the section for entering all the spare parts, there are 3 columns for part no., supplier and description. If the supplier code is 'stock', then a formula in the description column looks up the part no. in our stocklist workbook and returns the description of the part. This all works fine.
If the supplier code isn't 'stock' then the user must enter the description manually. This overwrites the formula but isn't a problem.
A problem occurs, however, if a non stock item is entered but then changed to a stock item afterwards. The formula has been overwritten and so doesn't return the description from the stocklist.
Is there any way to allow the value to be deleted from the cell without deleting the formula? Or is there a blindingly simple way to do this?!!

[Dave Peterson]

When I have to do things like this, I use multiple cells.

For instance:
Use A2 for Part number
Use B2 for supplier code
Use C2 for typed in Description
Use D2 for the description formula:
=if(b2="stock",vlookup(a2,sheet2!a:e,2,false),
if(c2<>"",c2,"Please enter your description"))

(or something like this)

And use the description from D2 in subsequent processing.

And you may want to add a validity check so that if B2="stock", then C2 has to
be blank.

Big Ant wrote:
>
> Hi,
> I've written a spreadsheet to help with quoting engineering work. In
> the section for entering all the spare parts, there are 3 columns for
> part no., supplier and description. If the supplier code is 'stock',
> then a formula in the description column looks up the part no. in our
> stocklist workbook and returns the description of the part. This all
> works fine.
> If the supplier code isn't 'stock' then the user must enter the
> description manually. This overwrites the formula but isn't a problem.
> A problem occurs, however, if a non stock item is entered but then
> changed to a stock item afterwards. The formula has been overwritten
> and so doesn't return the description from the stocklist.
> Is there any way to allow the value to be deleted from the cell without
> deleting the formula? Or is there a blindingly simple way to do this?!!
>
> --
> Big Ant
> ------------------------------------------------------------------------
> Big Ant's Profile: http://www.excelforum.com/member.php...o&userid=35750
> View this thread: http://www.excelforum.com/showthread...hreadid=555551

--

Dave Peterson

[Big Ant]

Thanks for that, I guess thats the best solution, I'll give it a go
Cheers!