Dependant? Data Validation

[matrixskydiver]

Hi all

I am sure there is someone out there who knows how to do what I am hoping to achieve.

I don't know how to get a data validation (B1) to be dependant on any figure being entered into a cell to the left of it (A1).

For Example:


(A1) ##,### ¦ (B1) Drop down ¦ (C1) Calculation dependant on drop down


A1 - will have a value entered.

B1 - I would like the action of populating A1 to prompt the user to select from a list of 4 items in a dropdown list in B1, i.e. the user will not be able to continue onto next row until the selection has been made.

The drop down list will have "High" "Low" "Med" "Firm"

C1 - This will be a percentage calculation e.g. IF A1="High"*75%

High: 75%, Low: 25%, Med: 50%, Firm: 100%

I already have a working calculation formulae, I just don't know how to get the data validation (B1) to be dependant on any figure being entered into A1.

I hope my explanation is ok and that someone can help me.

Kind regards

[mikerickson]

If nothing is entered in A1, what do you want the data validation on B1 to be?

If I understand the sequence of events you want:

The user enters anything in A1

A dialog box comes up requireing the user to choose between "Low", "Mid", "High" and "Firm"

The chosen value will be put in B1

C1 will be the product of A1 with .25, .5, .75 or 1, depending on the value in B1.

Please Login or Register  to view this content.

[matrixskydiver]

Hello mikerickson

Thank you for your quick reply.

If nothing is entered into A1 then nothing should happen. The purpose of this is to make sure that if a user enters any figure in A1 they are forced to make a selection from B1 so that C1 can be calculated.

I am not that advanced in my knowledge of excel and don't know how to implement the code that you have written.

I would really appreacicate your help by telling me if this is a macro, function or data validation.

I have attached a zipped file of a portion of the spreadsheet I am creating for you to see what I have done.

Many thanks

[mudraker]

matrixskydiver

Please read the forum rules

When you signed up to this site you agreed not to post the same question to multiple forums

Your duplicate thread - http://www.excelforum.com/showthread.php?t=626128 - has been closed

A thread with the rules is available at the top of each forum

[matrixskydiver]

Sorry for posting the tread twice. I thought the first one I posted was in the wrong place and I couldn't find it after I posted it.

[mudraker]

To place Private Sub Worksheet_Change macro in the correct location

'GoTo Excel
'Select sheet the macro is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

When installed in the correct module sheet the Worksheet_Change macro run evertime a change is made to a cell in the sheet

mikerickson macro does nothing if the change is not to A1
If A1 is changed it opens an Inputbox asking to enter a type - High , low etc (1st letter is enough eg H or h or L or f etc)
Depending op reply to Input box it multiplies A1 * a percentage & puts result in C1


In your Hide & Unhide macros you do not need to select columns & rows to hide unhide them

As an example these 2 lines of code

Please Login or Register  to view this content.

can be changed to

Please Login or Register  to view this content.

Macros that do not select columns etc run faster & are easier on the user as the screen does not jump around whilst the macro is running

[matrixskydiver]

That's great. Thank you for your help so far.

I have managed to do that, but realise that the example I gave in my original question was too vague.

How do I get this to apply to all of the cells in columns C, F, I, L, O, R, U, X & AA from row 7 down (for an infinate number of rows), so that the user is forced to enter the choice in the cell to the right of the one they are populating and the calculation then appearing in the cell to the right of the choice?

[mikerickson]

Good Morning!
This modification should act on the ranges you requested.

Please Login or Register  to view this content.

[matrixskydiver]

You are brilliant!!!!! It works!!!! I am totally grateful for all of your help. I will get serious gold stars at work on Monday.

Thank you.
XXXXX

[mudraker]

mikerickson

I like your use of Like to match column address.
I would never have though of that method.