Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help! Excel Formula
Below is an example of data in my excel sheet.
A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Entery number = 19 then print "Ricky 18 to 35 14/2/04 Spain" Please explain in step by step process, and in detail, im really a novice. Please help find me find the formula that solves this problem. thanks Ricky Samras |
#2
|
|||
|
|||
hi,
sorry. formulas return a value. they cannot perform an action like printout something. or copy/paste. you can do what you wish with a macro. but if you are a real novice as you say then i doubt that you would understand what is involed with that. but here it is. tested. works to use it. copy the macro for Private sub down in excel open the vb editor. Alt+F11 in the left pane, click your project to expand it. select the sheet. click the down arrow of the left combo box and select worksheet. paste the code there. close the editor and you are ready to go. good luck. you may need it. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("a2").Value = 9 Then Range(Range("A3"), Range("A3").Offset(0, 5)).Select ActiveSheet.PageSetup.PrintArea = Selection.Address ActiveWindow.SelectedSheets.PrintOut Copies:=1 Else If Range("a2").Value = 12 Then Range(Range("A4"), Range("A4").Offset(0, 5)).Select ActiveSheet.PageSetup.PrintArea = Selection.Address ActiveWindow.SelectedSheets.PrintOut Copies:=1 Else If Range("a5").Value = 19 Then Range(Range("A3"), Range("A5").Offset(0, 5)).Select ActiveSheet.PageSetup.PrintArea = Selection.Address ActiveWindow.SelectedSheets.PrintOut Copies:=1 End If End If End If End Sub -----Original Message----- Below is an example of data in my excel sheet. A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Entery number = 19 then print "Ricky 18 to 35 14/2/04 Spain" Please explain in step by step process, and in detail, im really a novice. Please help find me find the formula that solves this problem. thanks Ricky Samras . |
#3
|
|||
|
|||
Samrasr wrote:
Below is an example of data in my excel sheet. A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Entery number = 19 then print "Ricky 18 to 35 14/2/04 Spain" Please explain in step by step process, and in detail, im really a novice. Please help find me find the formula that solves this problem. thanks Ricky Samras Do a Data-Autofilter on column A -- Registered Linux User no 240308 Just waiting for Broadband to complete the conversion!(4 weeks and counting!) gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! |
#4
|
|||
|
|||
If you want to enter a value in A1 and in B1 have the whole sentance
returned to you then the following formula entered into A1 should work =INDEX(A2:F4,MATCH(A1,B2:B4),1)& " " &VLOOKUP(INDEX(A2:B4,MATCH(A1,B2:B4),1),A2:F4,2 ,0) & " " & VLOOKUP(INDEX(A2:B4,MATCH(A1,B2:B4),1),A2:F4,3,0) & " " & VLOOKUP(INDEX(A2:B4,MATCH(A1,B2:B4),1),A2:F4,4,0) & " " & TEXT(VLOOKUP(INDEX(A2:B4,MATCH(A1,B2:B4),1),A2:F4, 5,0), "dd/mm/yyyy") & " " & VLOOKUP(INDEX(A2:B4,MATCH(A1,B2:B4),1),A2:F4,6,0) Cheers JulieD "Samrasr" wrote in message ... Below is an example of data in my excel sheet. A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Entery number = 19 then print "Ricky 18 to 35 14/2/04 Spain" Please explain in step by step process, and in detail, im really a novice. Please help find me find the formula that solves this problem. thanks Ricky Samras |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |