data validation problem

N

neowok

What I need to be able to do is have a dropdown in the cells in a ro
where you can either select the cell value to be set as "N/A" OR se
the cell to be a given formula. Is this possible?

basically I have 5 columns which may need to have a formula in but the
will not all want the formula for every row, some rows will need to b
set to N/A. The problem then is that when someone inserts a new ro
underneath, I am guessing excel will not put the formulas into al
those 5 columns because the cell above it might have been set to N/A
meaning the users are going to have to keep trying to copy and past
formulas into the new row.

So I either need a way for the user to simply select N/A OR select th
formula for each of those cells from a dropdown, or i need t
automatically put the formula into those 5 columns every time a new ro
is inserted, and then the users can overwrite it with N/A if needed, s
they wont have to copy and paste anything.

Anyone know if any of this is possible? if not whats the easiest way t
do it? I need to make it quick and easy for people to enter new row
without having to muck about copying formulas
 
N

neowok

that solves the problem of excel not automatically copying formulas dow
to a new row but my problem is slightly more complicated in that i wan
set formulas in EVERY new row that are not nessessarily in the ro
above.

i.e.

E F G H I
===============
4 6 9 5 7
N/A 8 5 N/A 2
4 7 22 9 3

The numbers are all calculated by formulas. however sometimes I don
want the formula to apply and need to manually type in N/A instead.
but any new rows will need to have the formulas in each column b
default. i.e. if i was to try and insert a new row jus under the ro
containing the N/A's, i would need the new row to have the formulas i
every column, rather than excluding the ones with N/A above it.

I think what I may need to do is write a macro attached to a comman
button, the users enter N/A in the columns they dont want to apply
then click the button and the macro goes through the columns and if th
cell in the column is blank then insert the relevant formula, els
leave it alone.

Thats the only way I can see making it possible to have this kind o
setup without them manually copying an pasting formulas from othe
rows. Unless the macro you gave above can be modified so that instea
of copying formulas from above, it inserts a given formula in each o
the columns I need it in. This is probably the best way, I just don
know how I would modify that to insert a given formula into the colum
on the new row.

Thank
 
Top