Cell with formaula and a flag so cell has no formula

**76_shovel** · 03-17-2020, 11:47 AM

If it is thought this is possible I will post sample sheet.

Is it possible to have column cells contain formulas ( protected and locked ) and copied down and at the same
time be able to highlight a cell in this column and through the use of a flag or some other means allow this highlighted cell in this column become unprotected and allow text to be entered without affecting the other protected cells ?

Thanks

**jeffreybrown** · 03-17-2020, 12:09 PM

I would say what you ask could be possible, but we would need to see a workbook sample with a clear before and after.

Also, this would most likely require VBA. Do you want to use VBA?

Please see the "yellow" banner at top.

**76_shovel** · 03-17-2020, 01:22 PM

Thanks Jeff,
I will prepare sample work book with sample data and sample results and post it.

Regards,
Chuck

**76_shovel** · 03-17-2020, 07:58 PM

In our real work book we calculate work orders, this is a made up sample.

A formula base solution or macro or VB is acceptable if this is even possible.
I am running Office 97 so the solution needs to run on that.

In everyday use I run across the problem of having to enter data manually (even tho it exists on the data sheet)
because I have been unable to come up with a way to have a cell contain a formula (column M) on this sheet
and will work when I enter the labor code.

However if the labor code does not exist and I need to enter the data manually I can't because the cell
contains a formula and if I remove the formula then I can enter data but loose the "data" sheet data.

Hence the wish to have a flag or something that will allow a column cell to either have a formaula or become empty to allow manual entry.
If this is do able I would like both sides of the sheet to behave the same way.

The right side of the sheet is for part number entry of parts used and the description and price come from the
"data" sheet.

The left side of the sheet is used for services (labor) and have been entered totally manually.
For many of the traditional services the information for these services exist on the data sheet.

I would like to be able to enter the services number on the services side (if available) and let the
data come from the data sheet, AND if the service does not exist I would like to be able to enter the data manually
along with the Actual price and Hrs.

The light blue cells on the right side is where I have a sample of entry by service number entry. (description and price).
2 cells below the light blue cells I have entered 2 manual entries (nothing came from the data sheet).

On the left side rows 11 and 12 have part number data entry via the part number ( description, price) and in the
darker blue area at the bottom of the left side is where I have a manual entry of the part, description and prices.

On this sheet I also have a pull down for "Rate Change", we have 2 rates we use and I used this to figure the
correct rate to use. when manually adding data, I would like this to keep working.
It works with the manual made entries supplied as an example.

One Other pull down is "Estimate same", I was working on having this pull down work with the Est column ( rt side )
where if the actual time was different that the estimated time it would allow us to enter Est Time
other wise Est Time would = Actual time.
This one isnt critical, I can live with it as is if need be.

The data supplied has 5 entries for services and 5 entries for part numbers.

Regards,
Chuck

**jeffreybrown** · 03-18-2020, 07:54 AM

Hi Chuck,

There is way too much stuff going on here for me to keep it straight; however, you mention manually entering data. It doesn't make sense for the need to manually enter data on the WorkOrder tab.

All data should go on the data tab and then the WorkOrder tab would simply have formulas to collect the data. If you manually enter data on the WorkOrder tab, what if you need that data later on. Wouldn't this be a reason to add it on the data tab for use all times?

I'm sorry but this is very confusing

**76_shovel** · 03-18-2020, 04:16 PM

Jeff,
Sorry it is confusing, I guess I did not do well with explaining.
If Possible, can I try and simplify my wish and if a solution is available perhaps I can take it from there and finish what I was seeking.

Simplified request..
I am attaching a jpg for this request as you already have the .xls work book.
In column R you will find Hrs. This is where the actual hours is entered.

When work is done, if all went as planned the next column S is where the
same time that was put into column R will be put into column S by formula and
tied to the Estimate Same pull down being set to Yes.

If the time(s) are different my plan was to set the pull down called Estimate same
from Yes to No and what I wanted that to do was to allow us to change that ONE cell in column S
to not having a formula, thus we could manually enter the different time and not
loose the other formula calculated cells in column S.

I hope this has been made easier.

Chuck