Apply value to multiple cells

[Ritz]

I need a formula that states if cell F8=3, then K8, P8, U8, Z8, AE8, and AJ8=0 (if F8 is any other number, the other cells are left blank).

I can't write the formula with K8:AJ8, as there are hidden columns in-between with other formulas. HELP?

 

[Ed Acosta]

Put the formula in each of the individual cells to point to F8. So K8, P8, U8, Z8, AE8, AJ8 will have this formula in it

=if(F8=3,0,"")

 

[Ritz]

I can't put the formula in the other cells because they are manual entry cells and if F8 isn't 3 -- then the user will be populating the other cells w/the applicable data. I was wanting to put the formula in a totally different column that I could hide.

 

[lenze]

How is F8 populated?
lenze

 

[goliath624]

The quickest way I can think of is to have another column with the "Final Result", which has a formula that references both the user input column and the F8 value and determines what the result should be.

 

[Ritz]

Yeah!! I got you, you're my favorite answere guru as I find more helpful posts with your replies!!

F8 is manually entered. Basically I've created a grading worksheet. F8 is the column that captures if the weekly assignment was turned in (i.e., "0" if rec'd by Wed deadline; "1" if multiple pieces rec'd and at least one rec'd by Wed deadline; "2" if rec'd, but after Wed deadline and "3" if not rec'd at all). The other subsequent columns capture different information about the assignment. If no packet is rec'd however, I just want the other columns to auto fill w/a "0."

It's necessary to have something in the other columns, because I have another formula that won't produce a cumulative weekly grade if all 8 of the fields aren't populated (this is because several of the teachers were skipping a couple of the fields).

 

[lenze]

Give this a try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Row < 8 Then Exit Sub
Dim myR As Long
If Target = 3 Then
myR = Target.Row
Cells(myR, "K") = 0
Cells(myR, "P") = 0
Cells(myR, "U") = 0
'etc
End If
End Sub

lenze

 

[Ritz]

Works like a charm! I knew you'd have the answer. I've still got some reading to do to figure out all of the 'Dim' stuff before I'm anywhere near as bright as you, Lenze. Thanks!!