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

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

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


--
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