I have a spreadsheet where I want to put in a value in a cell, eg in cell A1.

Then from this value, 3 suggested prices are suggested, a low price, an average price and a high price. These are then displayed, in cell B1,B2,B3.

I then have another cell, C1, where validation is performed on the input to ensure that the input value lies within the low and high price, ie between B1 and B3.

So far so good.

However when I change the cell in A1, the cells in B1,B2,B3 change BUT the cell in C1 which was entered last time,might not lie within the B1 and B3 boundaries. The only way it can be corrected is if you try and type in a value in that cell.

What I would like to do, is by default, have cell C1 equal to cell B2, ie the average IF A1 is changed, however also allow the actual cell, C1 to be changed if need be. Is this possible in excel?
Can you allow a cell to have a default value BUT you can also enter your own value over it AND if A1 is changed, C1 gets overwritten with B2 again?

Thanks is advance.....

Had a read through and am a bit confuesed as to what you want to do !!!

If A1 generates cells B1,2,3 and C1 defaults to B2 unless A1 is changed ouside of B1 and B3..........it is impossible as the formulas are circular. An IF statement sounds like what your after but I'm afraid I don't understand what you need. If you give an example with numbers and what you expect it should be fairly easy.

Ok, I'm confused too, but I think I get the general gist.

A1 is where you want to say a cost price

B1 ... B3 then contain 3 suggested retail prices derived from this, where by B1 is the lowest and B3 the highest.

C1 is then the price that the item is sold at which must be validated to see that it falls within the range of B1...B3, this must default to what is in B2 until it is overwritten.

Ok, think I'm with you so far :)

My knowledge of Excel is limited, however ...

To start with C1 could directly point at the contents of B2 which means it will be defaulted with B2, however, if C1 is then later changed this link will be overwritten, any subsequent changes to A1 will not result in changes to C1 ... http://www.icrsystems.co.uk/smilies/headscratch.gif

I think what I would probably do is use to seperate cells, lets say for arguments sake, C1 and C2.

I'd use C1 to enter the value that I'm suggesting and then C2 would display the price it is to be sold at. This could then contain a formula that first checks to see if there is anything present in C1, if there is, it validates it with B1 & B3 and then either displays that, or displays an error. If C1 is empty then it can display the contents of B2.

To repeat, my knowledge of Excel is limited, it's one of those packages I pick up from time to time, then spend hours working out how to build the functions and formulas that I require :)

All the same, hope this helps :)

send me the sheet and i'll fix it.