If/Then or VBS???

S

Shane

I have a spreadsheet here that I am working on and need to have corresponding
cells filled in.

I have a list of names along the top row that corresponds with the same
exact names down the first column (in the same order). An "X" is placed along
someone's name in a row to show who their "partner" is -- along the top row.
I need to make sure that this "partner" also has the other person marked "X".
Basically, I need to automatically have Excel check that where an "X" is
placed, a corresponding "X" is also placed in another cell.

What formula or function should I use for this?
For instance --- If an "X" is in cell # B1 - Then place an "X" in # A2.
If an "X" is in cell # C1 Then place an "X" in # A3
....and on and on...

Any suggestions? I am lost and it's a BIG sheet!
 
D

Dave Peterson

How about an alternative?

This sounds like one of those distant between cities that you can find on an
atlas.

Most of these I see block out half of the chart (you could shade it so it looks
like it's not used.)

(I've found that when I try to duplicate data, I mess up. Better to use just
one copy and make sure that copy is correct.)
 
G

Gord Dibben

Shane

Some clarification is needed.......for me at least<g>

You state you have names in row 1 and same names in column A

Next you state there is an "X" in B1 and C1

Then you state you want an "X" in A2 and A3

What happened to the names in Row 1 and Column A?

Gord Dibben Excel MVP
 
D

Dave Peterson

Assuming that row 1 and column 1 contain names, you could use a worksheet event
that mirrors your typing:

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
Dim LastCol As Long
Dim myRng As Range
Dim myCell As Range

With Me
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("B2", .Cells(LastRow, LastCol))

If Intersect(Target, myRng) Is Nothing Then
'do nothing
Exit Sub
Else
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRng).Cells
If IsError(myCell.Value) Then
myCell.ClearContents
Else
If myCell.Column = myCell.Row Then
'can't partner with themselves!
myCell.ClearContents
Else
.Cells(myCell.Column, myCell.Row).Value _
= myCell.Value
End If
End If
Next myCell
Application.EnableEvents = True
End If
End With

End Sub


Back to excel to test it out.

Note that it doesn't look for multiple pairs--it just mimics your entry.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top