If statement to change cells

[Gummyworms1234]

I am trying to use an if statement to change cells to the location number in the corresponding row. I only need to apply the if statement to rows 10 and below. Rows 9 and above are using a different if statement linked to another tab. I want to change an 8 to the corresponding location for each row and if there’s an off in the cell, I want to change the cell to show x. I’ve tried a few variations but I didn’t have any luck with it. The first image shows how it currently looks and the second image is how I want it to be. Thanks for the help!

 

[Ezguy4u]

This should get the ball rolling. Let us know if you have any problems

Book1
	A	B	C	D	E	F	G	H
1
2
3
4	Location	sun	mon	tue	wed	thus	fri	sat
5	1001	1001	1001	1001	1001	x	x	x
6	1002	x	x	1002	1002	x	x	x
7	1003	1003	1003	1003	1003	x	x	x
8	1004	1004	x	x	x	x	x	x
9	1005	1005	1005	x	x	x	x	x
10	1006	8	8	8	8	off	off	off
11	1007	off	off	8	8	off	off	off
12	1008	8	8	8	8	off	off	off
13	1009	8	8	8	8	off	off	off
14	1010	8	off	off	off	off	off	off
15	1011	8	8	off	off	off	off	off
16
17
18
19
20	Location	sun	mon	tue	wed	thus	fri	sat
21	1001	1001	1001	1001	1001	x	x	x
22	1002	x	x	1002	1002	x	x	x
23	1003	1003	1003	1003	1003	x	x	x
24	1004	1004	x	x	x	x	x	x
25	1005	1005	1005	x	x	x	x	x
26	1006	1006	1006	1006	1006	x	x	x
27	1007	x	x	1007	1007	x	x	x
28	1008	1008	1008	1008	1008	x	x	x
29	1009	1009	1009	1009	1009	x	x	x
30	1010	1010	x	x	x	x	x	x
31	1011	1011	1011	x	x	x	x	x
If Statement

Cell Formulas
Range	Formula
B26:H31	B26	=IF(B10=8,$A26,IF(B10="off","x",""))

 

[Gummyworms1234]

The formula isn't doing what I thought it would do. In my head, I didn't think it needed to be applied to B26:H31 because that was how I wanted it to look like. Range B10:H15 is what I wanted to change. Can the formula be adjusted to work with that specific range starting with B10?

 

[Ezguy4u]

Gummyworms1234 To some degree I believe we all look at an Excel problem and think this should do what I am thinking it should do. But then when it doesn't work it's back to the drawing board. Now what you are suggesting would give us a circular error. We just can't put a formula in a cell with a reference to that same cell. Now the thing about excel is if we can't come in thru the front door we will just go around to the back door. Or a side door. There are just so many ways to accomplish what you want to do. Such as a VBA program just to name one. If you still want to keep the discussion going let us know.

Sub Change1()

Dim Row1 As Long
Dim Col1 As Long

For Row1 = 10 To 15
 For Col1 = 2 To 8
    If Cells(Row1, Col1) = 8 Then
        Cells(Row1, Col1) = Cells(Row1, 1)
    ElseIf Cells(Row1, Col1) = "off" Then
        Cells(Row1, Col1) = "x"
    End If
 Next Col1

Next Row1
End Sub

 

[Gummyworms1234]

Gummyworms1234 To some degree I believe we all look at an Excel problem and think this should do what I am thinking it should do. But then when it doesn't work it's back to the drawing board. Now what you are suggesting would give us a circular error. We just can't put a formula in a cell with a reference to that same cell. Now the thing about excel is if we can't come in thru the front door we will just go around to the back door. Or a side door. There are just so many ways to accomplish what you want to do. Such as a VBA program just to name one. If you still want to keep the discussion going let us know.

Sub Change1()

Dim Row1 As Long
Dim Col1 As Long

For Row1 = 10 To 15
For Col1 = 2 To 8
    If Cells(Row1, Col1) = 8 Then
        Cells(Row1, Col1) = Cells(Row1, 1)
    ElseIf Cells(Row1, Col1) = "off" Then
        Cells(Row1, Col1) = "x"
    End If
Next Col1

Next Row1
End Sub

VBA brings back bad memories of undergrad and I'm trying to avoid it. I actually found an easier way to do what I needed to do after browsing google. Thanks for the help!