Multiple If statement
 

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?

Multiple If statement
 

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

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

"John Gregory" wrote:

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?

Multiple If statement
 

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" wrote:

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?

Multiple If statement
 

That works, thank you

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

"FiluDlidu" wrote:

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

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

"John Gregory" wrote:

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?

Multiple If statement
 

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

"Pyrite" wrote:

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" wrote:

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?

Multiple If statement
 

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


"John Gregory" wrote:

That works, thank you

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

"FiluDlidu" wrote:

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

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

"John Gregory" wrote:

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?

Multiple If statement
 

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" wrote:

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


"John Gregory" wrote:

That works, thank you

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

"FiluDlidu" wrote:

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

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

"John Gregory" wrote:

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?

Multiple If statement
 

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" wrote:

That works, thank you

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

"FiluDlidu" wrote:

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

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

"John Gregory" wrote:

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?

Multiple If statement
 

Thank you for your help

"FiluDlidu" wrote:

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" wrote:

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


"John Gregory" wrote:

That works, thank you

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

"FiluDlidu" wrote:

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

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

"John Gregory" wrote:

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?