I have probably missed something simple??

  • Thread starter Thread starter seftonbarn
  • Start date Start date
S

seftonbarn

I need some help. For arguments sake my worksheet has only one column.
In row 1 is a Y/N field. If the user enters "N" in row 1 I want th
cell in row 2 to use a formula to sum a load of other totals in othe
worksheets. If row 1 is "Y" I want the user to be able to enter total
manually into row 2. I have achieved this rather crudely with
combination of a formula in row 2 that looks for the totals in th
other sheets and validation that only permits entry in row 2 if row
is "Y".

This is OK unless the user wants to change the Y to a N in row
expecting the formula to work. If this happens the formula is lost i
row 2 (having been overwritten manually). Any better ideas?? Thanks?
 
Hi
this kind of behaviour (a formula AND an manual entry in the same cell)
can't be done in Excel. The best workaround would be to use VBA (an
event procedure like worksheet_change)

But before doing this I would recommend using two different cells :-)
 
I think I'd use multiple columns (or rows--I'm a bit confused)).

Say you have descriptions in column A.
The formula values in column B.
The manual entries in column C.
Then in column D, I'd put this formula:

=if(trim(c2)<>"",c2,b2)

If the manual entry is used, then that's used.
If the manual entry is not used, then use the value returned by the formula.

But in any case, use the value in column D for future calculations.

And I'd use something like this as headers:

Description | Default Value | Override Value | Used Value
 
Back
Top