Multiple If statement

[John Gregory]

I am trying to write a "IF" test to do the following:

If the value of A1 = 1, then cell = B1
If the value of A1 = 2, then cell = B2
If the value of A1 = 3, then cell = B3

The cell A1 is used as the selector, and I have data that is in each of the
B cells (too much data for a list). I have tried data validation, but could
not get it to work.

Any ideas?

 

[FiluDlidu]

I'm not sure if that would work for you, but maybe that is what you are
looking for:

=index($B:$B,$A$1)

 

[Pyrite]

If you insist on doing it using IF then the following works:

=IF(A1=1,B1,IF(A1=2,B2,IF(A1=3,B3,"No Match")))

 

[John Gregory]

That works, thank you

By the way, is there a simple way to also copy the field shading from the
data cells?

 

[John Gregory]

Thank you for your response, I tried both ways and they both work well.

 

[FiluDlidu]

Through a macro that could be done, but I am unsure how much you like using
macros.

Would C1 be the cell where you enter your formula, you could use the
following on the sheet's code page:

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1,C1"), Target) Is Nothing Then Exit Sub
Range("B" & Range("A1").Value).Copy
Range("C1").PasteSpecial Paste:=xlPasteFormats
End Sub

 

[FiluDlidu]

Ha! You may want to add an extra line:

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1,C1"), Target) Is Nothing Then Exit Sub
Range("B" & Range("A1").Value).Copy
Range("C1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[FiluDlidu]

Or maybe:

Edit > Go to > =indirect("B"&A1)

That would bring you to cell B(whatever the number is in A1).

Copy

Go to the cell containing the formula:

Paste special > Formats

 

[John Gregory]

Thank you for your help

Ha! You may want to add an extra line:

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1,C1"), Target) Is Nothing Then Exit Sub
Range("B" & Range("A1").Value).Copy
Range("C1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub