| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple if Statement? | Excel Discussion (Misc queries) | |||
| Multiple IF statement | Excel Worksheet Functions | |||
| Multiple IF statement | Excel Discussion (Misc queries) | |||
| Multiple IF Statement | Excel Worksheet Functions | |||
| Multiple If Statement | Excel Worksheet Functions | |||
Linear Mode
