Posts by betapaul

Re: COUNTIF the date in one column is within the last 7 days of a date in another cel

I just get an error when I try that. "The formula you typed contains an error"

I'm stumped on this one.

This is what I need:
If the dates in I7:I106 are equal to "week ending date" (which will be changed every week in cell L3) or within the last 7 days of "week ending date", then COUNT cells J7:J106 if they equal the word "Deficient"

Any takers?

Re: If j6=any number then I6=--------

...the post title is exactly what I need, why would I change it again?

If J6="123" then I6="--------"

I need this formula to be in a third cell and it doesn't matter which one (N6 for example)...

Re: If j6=any number then I6=--------

Here is another instance.

If F6 equals "contacted", then I6 equals "x"

I need this formula to be placed in a hidden cell as both f6 and i6 need to be editable

I really did scour the Internet for this before posting cause I feel so dumb.

If J6 equals any number then I6 equals -------------

Only thing is, it needs to be done through conditional formatting OR in a third cell that I can hide. I can't lock either J6 or I6

So I have one cell with about 931 characters (all numbers). I want to separate them out in groups of 7 into 133 separate cells starting with the first number.

I know how to do it if I put

=mid(a1,1,7)

in all 133 cells and change the middle number, but I don't want to do that. Any other way?

Re: Set cell protection on/off based on other cell value for multiple sheets

I can't use data validation because I already am in K5:K24.

I tried this code and it's just not working. It works for I5 in relation to K5, but not for the cells going down the spreadsheet.

I believe the term I've seen used is "array" when discussing what I'm trying to do.

If I5 is blank, then K5 is LOCKED.
If I5 has "x" or "X", then K5 is unlocked.

If I6 is blank, then K6 is LOCKED.
If I6 has "x" or "X", then K6 is unlocked.

Etc, etc, all the way to I24 and K24

The tabs at the bottom are the days of the week and I need it to work on all tabs exactly like this.

Ok I have ran out of options. I'm trying to create a Macro that will let me do the following.

If I5:I24 equal "x", then K5:K24 are unlocked

If I5:I24 is blank or anything else, then K5:K24 are locked.

I don't need the entire K range locked if just I5 is blank or anything else. I need I5 to correspond with K5, I6 to correspond with K6, and so on...

In addition to this, I need this same macro to work on seven tabs in the workbook. SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

In addition, the sheets have to be protected as well.

This is what I have so far (if it's not in code I'm sorry this is my first post)

/Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Me.Range("I5")
       Case "X", "x"
       With Me
           .Unprotect
           .Range("K5").Locked = False
           .Range("I5").Locked = False
           .Protect
       End With
       Case Else
       With Me
           .Unprotect
           .Range("K5").Locked = True
           .Range("I5").Locked = False
           .Protect
       End With
   End Select

This only works for I5 and K5 cells. I can't figure out how to do it for an entire range or on multiple sheets.

Any help would be GREATLY appreciated.