QUESTION REGARDING LOCKING SPREADSHEETS AND PREFORMATTED CELLS

[TLAngelo]

I have a spreadsheet in which I have locked the columns that contain formulas
so that people who input data to the spreadsheet will not inadvertently type
over the formulas. The problem I am running into is that when someone
inserts a new row, the formulas need to carry into the inserted row, is there
a way to do that?

My other question is:

In my spreadsheet, I have preformatted one column to put "Yes" if the
processing times of 3 identified cells is less than or equal to .75. The
only problem is that it puts "Yes" in the cells that do not have any data
input into them yet. Is there a way to have it display "TBD" until someone
enters data into that row?

[SimonCC]

For question 1, I don't think there's a way to do so easily unless macros is
involved.

For question 2, try:
=IF(OR(A1="",B1="",C1=""),"TBD",IF(SUM(A1:C1)<=.75,"Yes","No"))

-Simon

"TLAngelo" wrote:

> I have a spreadsheet in which I have locked the columns that contain formulas
> so that people who input data to the spreadsheet will not inadvertently type
> over the formulas. The problem I am running into is that when someone
> inserts a new row, the formulas need to carry into the inserted row, is there
> a way to do that?
>
> My other question is:
>
> In my spreadsheet, I have preformatted one column to put "Yes" if the
> processing times of 3 identified cells is less than or equal to .75. The
> only problem is that it puts "Yes" in the cells that do not have any data
> input into them yet. Is there a way to have it display "TBD" until someone
> enters data into that row?

[TLAngelo]

Thank you! Your answer to my second question helped. Can you help with the
macro to automatically formulate the added rows?

"SimonCC" wrote:

> For question 1, I don't think there's a way to do so easily unless macros is
> involved.
>
> For question 2, try:
> =IF(OR(A1="",B1="",C1=""),"TBD",IF(SUM(A1:C1)<=.75,"Yes","No"))
>
> -Simon
>
> "TLAngelo" wrote:
>
> > I have a spreadsheet in which I have locked the columns that contain formulas
> > so that people who input data to the spreadsheet will not inadvertently type
> > over the formulas. The problem I am running into is that when someone
> > inserts a new row, the formulas need to carry into the inserted row, is there
> > a way to do that?
> >
> > My other question is:
> >
> > In my spreadsheet, I have preformatted one column to put "Yes" if the
> > processing times of 3 identified cells is less than or equal to .75. The
> > only problem is that it puts "Yes" in the cells that do not have any data
> > input into them yet. Is there a way to have it display "TBD" until someone
> > enters data into that row?

[Gord Dibben]

David McRithchie has written code to insert rows and copy formulas to the new
rows.

http://www.mvps.org/dmcritchie/excel/insrtrow.htm


Gord Dibben MS Excel MVP

On Wed, 14 Jun 2006 08:28:02 -0700, TLAngelo
<[email protected]> wrote:

>Thank you! Your answer to my second question helped. Can you help with the
>macro to automatically formulate the added rows?
>
>"SimonCC" wrote:
>
>> For question 1, I don't think there's a way to do so easily unless macros is
>> involved.
>>
>> For question 2, try:
>> =IF(OR(A1="",B1="",C1=""),"TBD",IF(SUM(A1:C1)<=.75,"Yes","No"))
>>
>> -Simon
>>
>> "TLAngelo" wrote:
>>
>> > I have a spreadsheet in which I have locked the columns that contain formulas
>> > so that people who input data to the spreadsheet will not inadvertently type
>> > over the formulas. The problem I am running into is that when someone
>> > inserts a new row, the formulas need to carry into the inserted row, is there
>> > a way to do that?
>> >
>> > My other question is:
>> >
>> > In my spreadsheet, I have preformatted one column to put "Yes" if the
>> > processing times of 3 identified cells is less than or equal to .75. The
>> > only problem is that it puts "Yes" in the cells that do not have any data
>> > input into them yet. Is there a way to have it display "TBD" until someone
>> > enters data into that row?

[TLAngelo]

Excellent, thank you! It works wonderfully!!

"Gord Dibben" wrote:

> David McRithchie has written code to insert rows and copy formulas to the new
> rows.
>
> http://www.mvps.org/dmcritchie/excel/insrtrow.htm
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 14 Jun 2006 08:28:02 -0700, TLAngelo
> <[email protected]> wrote:
>
> >Thank you! Your answer to my second question helped. Can you help with the
> >macro to automatically formulate the added rows?
> >
> >"SimonCC" wrote:
> >
> >> For question 1, I don't think there's a way to do so easily unless macros is
> >> involved.
> >>
> >> For question 2, try:
> >> =IF(OR(A1="",B1="",C1=""),"TBD",IF(SUM(A1:C1)<=.75,"Yes","No"))
> >>
> >> -Simon
> >>
> >> "TLAngelo" wrote:
> >>
> >> > I have a spreadsheet in which I have locked the columns that contain formulas
> >> > so that people who input data to the spreadsheet will not inadvertently type
> >> > over the formulas. The problem I am running into is that when someone
> >> > inserts a new row, the formulas need to carry into the inserted row, is there
> >> > a way to do that?
> >> >
> >> > My other question is:
> >> >
> >> > In my spreadsheet, I have preformatted one column to put "Yes" if the
> >> > processing times of 3 identified cells is less than or equal to .75. The
> >> > only problem is that it puts "Yes" in the cells that do not have any data
> >> > input into them yet. Is there a way to have it display "TBD" until someone
> >> > enters data into that row?
>
>

[Gord Dibben]

Thanks for the feedback.

I'm sure David appreciates the kudos.


Gord Dibben MS Excel MVP
On Wed, 14 Jun 2006 14:49:01 -0700, TLAngelo
<[email protected]> wrote:

>Excellent, thank you! It works wonderfully!!
>
>"Gord Dibben" wrote:
>
>> David McRithchie has written code to insert rows and copy formulas to the new
>> rows.
>>
>> http://www.mvps.org/dmcritchie/excel/insrtrow.htm
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Wed, 14 Jun 2006 08:28:02 -0700, TLAngelo
>> <[email protected]> wrote:
>>
>> >Thank you! Your answer to my second question helped. Can you help with the
>> >macro to automatically formulate the added rows?
>> >
>> >"SimonCC" wrote:
>> >
>> >> For question 1, I don't think there's a way to do so easily unless macros is
>> >> involved.
>> >>
>> >> For question 2, try:
>> >> =IF(OR(A1="",B1="",C1=""),"TBD",IF(SUM(A1:C1)<=.75,"Yes","No"))
>> >>
>> >> -Simon
>> >>
>> >> "TLAngelo" wrote:
>> >>
>> >> > I have a spreadsheet in which I have locked the columns that contain formulas
>> >> > so that people who input data to the spreadsheet will not inadvertently type
>> >> > over the formulas. The problem I am running into is that when someone
>> >> > inserts a new row, the formulas need to carry into the inserted row, is there
>> >> > a way to do that?
>> >> >
>> >> > My other question is:
>> >> >
>> >> > In my spreadsheet, I have preformatted one column to put "Yes" if the
>> >> > processing times of 3 identified cells is less than or equal to .75. The
>> >> > only problem is that it puts "Yes" in the cells that do not have any data
>> >> > input into them yet. Is there a way to have it display "TBD" until someone
>> >> > enters data into that row?
>>
>>