XL2000: Edit cell with existing sheet reference

[locutis]

OK, here's another one for your Excel experts.

Can someone tell me how to set-up a cell so that the user can override the original cell reference (='Sheet1'!B2) with a manual input number, yet still leave the original cell reference intact when they leave the cell. I don't want to protect the cell because in some cases the cell that is populated by the reference from the lookup sheet will need to be overwritten, and if I protect the cell the user can't input.

Thanks in advance,
Jean Luc

 

[SkipVought]

Hi,

The short answer is, "No!" Not without VBA.

But here's a way to accompish the same thing.

Your formula is
[tt]
='Sheet1'!B2
[/tt]
Let's assume that it's in Sheet1!C2. If you enter a value in C2, the formula is gone.

However, let's say that your formula were, instead...
[tt]
=if(isblank(Sheet1!A2),'Sheet1'!B2,Sheet1!A2)
[/tt]
So you OVEFRRIDE the value in A2 instead of C2 (which ought to be PROTECTED)

Skip,
[sub]
[red][/red]
[/sub]