Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
I would like to apply a formula to the entire column, but this
increases the size of the spreadsheet and also is a bit messy when printing. When I enter data into B5 for example, I would like C5 to automatically enter the vlookup formula I have written. Any Visual basic commands I could try? Thanks again guys for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Hi
is a bit messy when printing. Presumably, you mean because the sheet shows a series of errors where there are no entries in column B. Amend your formula in C5 to =IF(B5="","", your_formula ) and copy down as far as required. Nothing with show in column C until entries are made in the corresponding cell of column B -- Regards Roger Govier "tommylux" wrote in message oups.com... I would like to apply a formula to the entire column, but this increases the size of the spreadsheet and also is a bit messy when printing. When I enter data into B5 for example, I would like C5 to automatically enter the vlookup formula I have written. Any Visual basic commands I could try? Thanks again guys for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Click on Tools|Options, select the Edit tab, and make sure that Extend List
formats and Formulas is ticked. As you go down, this will automatically extend your formulas "tommylux" wrote: I would like to apply a formula to the entire column, but this increases the size of the spreadsheet and also is a bit messy when printing. When I enter data into B5 for example, I would like C5 to automatically enter the vlookup formula I have written. Any Visual basic commands I could try? Thanks again guys for your help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Hi Kassie
I think this only works in XL2003 (and presumably 2007) and only if you have created a List with DataList. -- Regards Roger Govier "kassie" wrote in message ... Click on Tools|Options, select the Edit tab, and make sure that Extend List formats and Formulas is ticked. As you go down, this will automatically extend your formulas "tommylux" wrote: I would like to apply a formula to the entire column, but this increases the size of the spreadsheet and also is a bit messy when printing. When I enter data into B5 for example, I would like C5 to automatically enter the vlookup formula I have written. Any Visual basic commands I could try? Thanks again guys for your help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Kassie, It is already ticked as default, havn't been sucsessful.
I might try the VB out, Thanks kassie wrote: Click on Tools|Options, select the Edit tab, and make sure that Extend List formats and Formulas is ticked. As you go down, this will automatically extend your formulas |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
This Sheet Code worked for me..
Note that my example named the LookupRange MyRange, and I used 3 as my "bring-back" column << You should change Both these to suit (MyRange & 3) HTH Jim May Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1).Value = "=Vlookup(" & Target & ",MyRange,3,False)" End If End Sub "tommylux" wrote in message oups.com: I would like to apply a formula to the entire column, but this increases the size of the spreadsheet and also is a bit messy when printing. When I enter data into B5 for example, I would like C5 to automatically enter the vlookup formula I have written. Any Visual basic commands I could try? Thanks again guys for your help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Jim, my actual code:
Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Else Target.Offset(0, 1).Formula = "IF(OR(ISBLANK(" & Target & ")," & Target & "'NA'),"",VLOOKUP(" & Target & ",'Batch Links'!A:H,8,FALSE))" End If End Sub 1. How do you put the Cell Address in the code above? Target simply puts the value of the cell. 2. Doesnt matter what I do, the code only puts in the following text: IF(OR(ISBLANK(23),23'NA'),",VLOOKUP(23,'Batch Links'!A:H,8,FALSE)) without the "=" to make it a formula. Jim May wrote: This Sheet Code worked for me.. Note that my example named the LookupRange MyRange, and I used 3 as my "bring-back" column << You should change Both these to suit (MyRange & 3) HTH Jim May Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1).Value = "=Vlookup(" & Target & ",MyRange,3,False)" End If End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Quick answer, headed for bed.. (right now), but
Substitute.. ..Formula = "IF... With ..Value = "=IF... HTH "tommylux" wrote in message oups.com: Jim, my actual code: Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Else Target.Offset(0, 1).Formula = "IF(OR(ISBLANK(" & Target & ")," & Target & "'NA'),"",VLOOKUP(" & Target & ",'Batch Links'!A:H,8,FALSE))" End If End Sub 1. How do you put the Cell Address in the code above? Target simply puts the value of the cell. 2. Doesnt matter what I do, the code only puts in the following text: IF(OR(ISBLANK(23),23'NA'),",VLOOKUP(23,'Batch Links'!A:H,8,FALSE)) without the "=" to make it a formula. Jim May wrote: This Sheet Code worked for me.. Note that my example named the LookupRange MyRange, and I used 3 as my "bring-back" column << You should change Both these to suit (MyRange & 3) HTH Jim May Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1).Value = "=Vlookup(" & Target & ",MyRange,3,False)" End If End Sub |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
I like to let excel build my addresses for me. Then I don't have to worry about
the syntax (when do I need apostrophes, where does the ! go?). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myLookupRng As Range With Target If .Cells.Count 1 Then Exit Sub If Application.Intersect(.Cells, Range("F:F")) Is Nothing Then Exit Sub End If Set myLookupRng = Me.Parent.Worksheets("Batch Links").Range("A:H") .Offset(0, 1).Formula _ = "=IF(OR(ISBLANK(" & .Address & "),isna(" & .Address _ & ")),"""",VLOOKUP(" & .Address & "," _ & myLookupRng.Address(external:=True) & ",8,FALSE))" End With End Sub tommylux wrote: Jim, my actual code: Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Else Target.Offset(0, 1).Formula = "IF(OR(ISBLANK(" & Target & ")," & Target & "'NA'),"",VLOOKUP(" & Target & ",'Batch Links'!A:H,8,FALSE))" End If End Sub 1. How do you put the Cell Address in the code above? Target simply puts the value of the cell. 2. Doesnt matter what I do, the code only puts in the following text: IF(OR(ISBLANK(23),23'NA'),",VLOOKUP(23,'Batch Links'!A:H,8,FALSE)) without the "=" to make it a formula. Jim May wrote: This Sheet Code worked for me.. Note that my example named the LookupRange MyRange, and I used 3 as my "bring-back" column << You should change Both these to suit (MyRange & 3) HTH Jim May Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1).Value = "=Vlookup(" & Target & ",MyRange,3,False)" End If End Sub -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Thanks dave,
Just what I was looking for, I have changed it a little you may have confused "NA" for isna which is what the user would type in, forgive the formatting: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myLookupRng As Range With Target If .Cells.Count 1 Then Exit Sub If Application.Intersect(.Cells, Range("F:F")) Is Nothing Then Exit Sub End If If Application.Intersect(.Cells, Range("1:1")) Is Nothing Then If Target.Formula = "" Then .Offset(0, 1).Formula = "" Else Set myLookupRng = Me.Parent.Worksheets("Batch Links").Range("A:H") .Offset(0, 1).Formula _ = "=IF(OR(ISBLANK(" & .Address & ")," & .Address & "=""NA""),"""",VLOOKUP(" & .Address & "," & myLookupRng.Address(external:=True) & ",8,FALSE))" End If End If End With End Sub |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formula automatically down when date is entered
Sorry for guessing wrong about the =na()/NA stuff.
But glad you got it working. tommylux wrote: Thanks dave, Just what I was looking for, I have changed it a little you may have confused "NA" for isna which is what the user would type in, forgive the formatting: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myLookupRng As Range With Target If .Cells.Count 1 Then Exit Sub If Application.Intersect(.Cells, Range("F:F")) Is Nothing Then Exit Sub End If If Application.Intersect(.Cells, Range("1:1")) Is Nothing Then If Target.Formula = "" Then .Offset(0, 1).Formula = "" Else Set myLookupRng = Me.Parent.Worksheets("Batch Links").Range("A:H") .Offset(0, 1).Formula _ = "=IF(OR(ISBLANK(" & .Address & ")," & .Address & "=""NA""),"""",VLOOKUP(" & .Address & "," & myLookupRng.Address(external:=True) & ",8,FALSE))" End If End If End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Trending Formula Results by Date | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula to calucate # of months based on a speificed date entered | Excel Worksheet Functions |