Cell Data Validation

one_more_cave_dweller

Board Regular
Joined
Sep 27, 2004
Messages
118
I would like to require the user to enter a number into a2 before they can enter a number in a1. How can I do this

Thanks :pray:
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
You could use code. Right-click on the Sheet's tab and paste in the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Address = "$A$1" And Range("A2") = "" Then
Range("A2").Select
End If
End Sub

You may have to go to Tools->Macro->Security, set the security level to Medium, save the file, close and reopen the file and click Enable Macros. But what it does is if you click A1 (and only A1) and A2 is empty, it select A2. I'm sure there are other ways to do the same thing, but I can't think of a way without code.
 

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
You could also use conditional formatting so that the text in A1 is white until you put something in A2, but that wouldn't really help :)
 

swst

Board Regular
Joined
Jan 17, 2005
Messages
186
Could have this

In cell A3
=if(A1="","Data has not been entered in cell A1",A2)

Cell A3 would then be used for any ongoing calculations
 

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547

ADVERTISEMENT

Data->Validation is used to make sure you enter appropriate values, but it cannot be used to allow/disallow you from entering values.
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
vconfused said:
Data->Validation is used to make sure you enter appropriate values, but it cannot be used to allow/disallow you from entering values.

Data/Validation most certainly can allow/disallow you from entering values.

Select A1. In Data/Validation choose custom and use this formula:-

=AND(ISNUMBER(A1),ISNUMBER(A2))

Then, uncheck the box that says "ignore blank".
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
ken2step said:
Also....

=ISNUMBER(A2) Uncheck the box "ignore blank"

works as well

Not exactly. The OP specified he wanted numbers entered into A1 and A2. My solution ensures that the user enters a number into A2 and can only enter a number into A1 (if A2 has a number, of course).

Your solution will allow text to be entered into A1.
 

Forum statistics

Threads
1,147,679
Messages
5,742,575
Members
423,738
Latest member
AshleyKitsune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top