Cell Validation
 

I'm working on a sheet that needs some validation. For example, in cell
C4 they are supposed to enter their initials, but some enter their full
name. I need to make sure that they enter (for example) ABC instead of
Alex Billy Charlie.

Then, they are supposed to enter the week ending date in cell K4. I'm
wanting to add a calendar there, so they can select the date. However,
I only want my calendar to show week ending Saturdays, or make it so
when they select any day in that week, it selects that weeks Saturday.
Then, when they enter the date, it will automatically fill in
information in G10 (Sunday), G21 (Monday), G46 (Tuesday), G71
(Wednesday), etc... This will be the rest of the days in that
particular week..


example:
Enter date 06-18-05 in cell K4
G10 = 06-12-05
G21 = 06-13-05
G46 = 06-14-05
G71 = 06-15-05.... etc....




Sorry if the description is vague...Thanks in advance for any help!!


DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

For the first part, couldn't you use data|Validation and only allow text whose
length is less than 3 (or 4)?

For the second part, I'd let the user type the date they want. Then I'd use a
helper (adjacent???) cell with this formula:

=A1+7-WEEKDAY(A1)

I'd format it nicely:
dddd mm/dd/yyyy

So that the users could see that it was indeed Saturday.



DejaVu wrote:

I'm working on a sheet that needs some validation. For example, in cell
C4 they are supposed to enter their initials, but some enter their full
name. I need to make sure that they enter (for example) ABC instead of
Alex Billy Charlie.

Then, they are supposed to enter the week ending date in cell K4. I'm
wanting to add a calendar there, so they can select the date. However,
I only want my calendar to show week ending Saturdays, or make it so
when they select any day in that week, it selects that weeks Saturday.
Then, when they enter the date, it will automatically fill in
information in G10 (Sunday), G21 (Monday), G46 (Tuesday), G71
(Wednesday), etc... This will be the rest of the days in that
particular week..

example:
Enter date 06-18-05 in cell K4
G10 = 06-12-05
G21 = 06-13-05
G46 = 06-14-05
G71 = 06-15-05.... etc....

Sorry if the description is vague...Thanks in advance for any help!!

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

--

Dave Peterson

Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!!
Thanks again for your help.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

I think I'd yell at the users not to do use that technique anymore.

But if I had to, I'd have a worksheet event looking for changes and fix those
cells with just spaces entered.

Rightclick on the worksheet tab that should have this behavior and select View
code. Then paste this into that code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

On Error Resume Next
Application.EnableEvents = False
For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If Trim(myCell.Value) = "" Then
myCell.Value = ""
End If
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

This doesn't clean up the existing bad cells--it just stops any new cells from
being cleared this way.



DejaVu wrote:

Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!!
Thanks again for your help.

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

--

Dave Peterson

Thanks Dave,

That worked perfectly for not allowing spaces!!!
Thanks for all your help!!



DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

Dave, I have another question for you.

I have several cells that come from the value of cell K4. For example,
the other cells have formulas like:
=IF($K$4="","",$K$4-6)
=IF($K$4="","",$K$4-5)
...etc


In cell K4, they are supposed to enter the week ending Saturday,
because each of the following cells depend on that date. I'm wondering
what is the best way to make sure that they enter a Saturday? Right
now, I have the cell formatted as dddd mm/dd/yyyy.

Should I add a drop down calendar to this cell?

Thanks again for all your help Dave!!


DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

You could use data|validation like:


DejaVu wrote:

Dave, I have another question for you.

I have several cells that come from the value of cell K4. For example,
the other cells have formulas like:
=IF($K$4="","",$K$4-6)
=IF($K$4="","",$K$4-5)
..etc

In cell K4, they are supposed to enter the week ending Saturday,
because each of the following cells depend on that date. I'm wondering
what is the best way to make sure that they enter a Saturday? Right
now, I have the cell formatted as dddd mm/dd/yyyy.

Should I add a drop down calendar to this cell?

Thanks again for all your help Dave!!

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

--

Dave Peterson

You could use data|validation like:

=WEEKDAY(A1)=6

But if you want that calendar, take a look at Ron de Bruin's site for some
tips/code/free calendar control:
http://www.rondebruin.nl/calendar.htm

But why not just let the user type what they want and use that other cell to get
the associated Friday?



DejaVu wrote:

Dave, I have another question for you.

I have several cells that come from the value of cell K4. For example,
the other cells have formulas like:
=IF($K$4="","",$K$4-6)
=IF($K$4="","",$K$4-5)
..etc

In cell K4, they are supposed to enter the week ending Saturday,
because each of the following cells depend on that date. I'm wondering
what is the best way to make sure that they enter a Saturday? Right
now, I have the cell formatted as dddd mm/dd/yyyy.

Should I add a drop down calendar to this cell?

Thanks again for all your help Dave!!

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

--

Dave Peterson