data validation problem
 

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

--
Message posted from http://www.ExcelForum.com

data validation problem
 

Hi
a solution for inserting rows with formulas:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

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

basically I have 5 columns which may need to have a
formula in but they
will not all want the formula for every row, some rows
will need to be
set to N/A. The problem then is that when someone
inserts a new row
underneath, I am guessing excel will not put the formulas
into all
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 paste
formulas into the new row.

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

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


---
Message posted from http://www.ExcelForum.com/

.

data validation problem
 

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

--
Message posted from http://www.ExcelForum.com