How do you protect cells on condition of other cells having data inputted?

**rickd909** · 12-04-2006, 07:19 AM

I need to find a way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa). Can anyone help?

Rick

**Carim** · 12-04-2006, 09:02 AM

Hi,

As far as the principle is concerned, it is feasible with an event macro attached to the worksheet ...
Shoud you need a precise answer, please upload a zipped copy of your worksheet ...

HTH
Carim

**rickd909** · 12-05-2006, 12:59 PM

Thanks Carim.

I have uploaded a slimmed down version of my file as it consisted of many sheets. Essentially, I want to find out how to protect arrays of cells when i input data in certain cells, to prevent inaccurate data inputting. Hopefully my sheets are self explanatory. Give me a shout if you need to know any other information.

PS this is my first go at a 'bigger sheet'

Regards Rick

**rickd909** · 12-08-2006, 06:55 AM

I'm still having big problems with this. Can anyone help me out?

**Carim** · 12-08-2006, 07:35 AM

Hi,

But where is the "slimmed down" version of your zipped file ...???

Carim

**rickd909** · 12-08-2006, 12:06 PM

Oh. I will try again.

**Carim** · 12-10-2006, 02:38 PM

Rick,

If you don't mind a couple of remarks ...

1. Uploading a file without removing worksheet password is not going to help

2. Not being specific about what you expect is not going to help ... either

Please take the time to clearly express your request, in order to get the appropriate answer ...

HTH
Carim

**rickd909** · 12-11-2006, 07:46 AM

Hi Carim,

Thank you for taking the time of downloading the sheet and bringing these points to my attention.

To explain a bit of background, I am collecting data on people who use a counselling service. I am capturing whether they are an employee or a dependant and whether they had telephone counselling or face to face counselling. This is done on worksheet 'Cont. User Analysis (1)'. The contacts row at the top (c1-w1) is a reference number for each client and I input a '1' in the necessary cell. So for client 34, if you read vertically you will see that they are an employee and they used face to face counselling.

The worksheet entitled 'statistics (1)' is for collecting more specific information pertaining to each client (demographics, how they used the service, etc..). I have set it up so as each table in the worksheet automatically takes the client number from the 'Cont. User Analysis (1)' that is needed for that particular table and enters it in the orange row at the top of each table. For example, client 36 does not appear in the demographics table as I do not want to collect data on dependants.

Turning to my query, lets say that I want to mark client 34 as an employee. What I have done is enter a '1' in cell C3 on worksheet 'Cont. User Analysis'. Now, I would like the fact that I have done this to protect cell C4 as a client cannot be an employee and a dependant. As you can see, I also need it to work the other way around. If C4 is entered with a '1', then C3 becomes protected.

On the worksheet 'statistics (1)', I want to implement this principle in many places. For instance, if the client is male he cannot be female, or if a client is married, he cannot be single/widowed/divorced. If the client is in the age range 16-24, he cannot be in any other age range.

Currently a '1' can be entered haph-hazardly anywhere on both these sheets (which you will be able to see for yourself just by doing it!), which may introduce errors in the calculations (i.e someone who is both male and female etc..). I have used data validation so as only a '1' can be input into the arrays where I want to input data to prevent other numbers, words, letters, etc.. to be input. This goes someway to prevent errors in the calculation but I need to get it as watertight as possible.

The 'Overview' sheet may be ignored as this just pulls data from all the other sheets to present an annual calculation of all the data. As I have 'cut down' the size of the workbook for uplaod, this is why #REF! appears.

I hope this clarifies what I am trying to do. If not please feel free to ask further questions. I really do appreciate you spending time looking into my problem.

If you need to unprotect the sheet, the password is 'password'

Kind Regards

Rick

**Carim** · 12-12-2006, 04:10 AM

Hi Rick,

I do understand your situation and the problem you want to solve ...
My belief is that you are over-complicating your own life ...
What you are asking for is feasible, say for two mutually exclusive cells ...

Please Login or Register  to view this content.

BUT ... for seven mutually exclusive cells, you will need a lot of patience...

My recommendation would be to have as many validation lists as needed, stored in a Validation sheet, and all your fields with dropdown boxes would automatically become "mutually exclusive cells" ...

Hope I have answered your question ...

HTH
Carim

**rickd909** · 12-12-2006, 01:37 PM

Thanks Carim. I'll look into doing that. I did not realise that using the principle that I had in mind was so long winded!!

Thanks again

Rick

**Carim** · 12-12-2006, 01:57 PM

You are welcome ...

Glad you are going the "validation" way ...

Do not hesitate to drop a post in the forum, should you need help ...

Cheers
Carim

**rickd909** · 01-10-2007, 11:41 AM

Hello, I'm back.

Thanks for your excellent advice Carim. I have now re-formatted my spreadsheet using validation lists.

My new problem is this:

I have two rows that look like this:

A B C D E F G
1 Emp Emp Emp Dep Dep Emp Dep
2 Tel f2f f 2f f2f Tel Tel Tel

I need the formula that will count the number of 'Tel's if the cell above it is 'Emp'... Help would be truly appreciated.

Rick

**Carim** · 01-10-2007, 12:04 PM

Hi Rick,

Why don't you upload a zipped copy of your worksheet ...
It will be much faster ...

**Carim** · 01-11-2007, 04:46 AM

Hi Rick,

Please Login or Register  to view this content.

**rickd909** · 01-12-2007, 09:27 AM

Wow Carim, you da man!! That does exactly what I want it to. I have looked in the Help menu at this function and I can't see why it should work. What is actually happening in this formula?

**Carim** · 01-12-2007, 10:46 AM

You are welcome

Thanks for the feedback

In my opinion, the best explanation about the sumproduct() function is provided by Bob

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**rickd909** · 01-16-2007, 11:15 AM

Thanks for the link Carim. The SUMPRODUCT info allowed me to further refine my count.

I would also like to add into this formula a further condition and I was wondering if you could tell me whether it is possible. I have attached a copy of the worksheet for simplicity.

I would like to use the SUMPRODUCT function only on the condition that a date field falls between (or on) two dates specified in other cells.

So, on my attached workbook, the sheet entitled 'Report 1' has cells C12 and C13 for dates. These are my specified dates that I want to count data between.

On the 'Util Data 1' sheet C103:V103 are used for inputting dates. Note that I have set up the sheet so that with the dates in 'Report1' C12 and C13, 'Util Data 1' H103 should not be considered at all in the count because it lies outside of the date range.

It may be worth noting that the SUMPRODUCT formulae are on 'Util Data 1' C98, C99, C100, C101 and it is each of these that I would like to update to take account of the date range.

Is this possible?

Thanks

Rick

**Carim** · 01-16-2007, 11:33 AM

Hi,

Yes ... it is possible ... but where is located the formula to be amended ...
I cannot find it ...

**Carim** · 01-16-2007, 11:42 AM

Hi again ...

Found it in cell W98 ...
Attached is the amendment ...

**rickd909** · 01-16-2007, 12:24 PM

Thank you once again Carim.

**Carim** · 01-16-2007, 12:25 PM

You are welcome ...

You are going to become the top expert of the sumproduct() function ...

**rickd909** · 01-18-2007, 06:41 AM

Thanks for the link Carim.

I have read Bob's explanation of the SUMPRODUCT function, and I understand it all apart from 'double unary' operator. I noted that when you amended my formula you substituted the double unary operator for my '*' operator.

Could you, or anyone, tell me precisely what 'double unary' is?

Thanks

Rick...

**Carim** · 01-18-2007, 06:51 AM

Hi Rick,

Since sumproduct() is basically designed to work with numbers, the double unary operator takes care of text and transforms them into true or false, or rather into 1 and 0 ... in order to allow calculation ...

**Scuba Wino** · 01-18-2007, 06:44 PM

Originally Posted by Carim

You are welcome

Thanks for the feedback

In my opinion, the best explanation about the sumproduct() function is provided by Bob

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Now that was a great read.

Thanks for sharing that. I think I need to use more SUMPRODUCT, it will make my life a little easier.

Good read!