Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for all your help, much appreciated.
Dave Peterson wrote: Sorry for guessing wrong about the =na()/NA stuff. But glad you got it working. |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Another quick question guys, if possible,
How do I add to this code so that only the value of the formula is entered into the cell automatically, rather than the formula itself? Hope for your quick response. Tom |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You may want to include more information.
For me (and probably lots of others), the older posts have aged off and the context of your follow up is lost. tommylux wrote: Another quick question guys, if possible, How do I add to this code so that only the value of the formula is entered into the cell automatically, rather than the formula itself? Hope for your quick response. Tom -- Dave Peterson |
#15
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In , Dave Peterson
spake thusly: tommylux wrote: How do I add to this code so that only the value of the formula is entered into the cell automatically, rather than the formula itself? For me (and probably lots of others), the older posts have aged off and the context of your follow up is lost. Here you go: In .com, tommylux spake thusly: 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 |
#16
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
This line adds the formula:
.Offset(0, 1).Formula _ = "=IF(OR(ISBLANK(" & .Address & ")," & .Address _ & "=""NA""),"""",VLOOKUP(" & .Address & "," & _ myLookupRng.Address(external:=True) & ",8,FALSE))" The OP can change it so that it plops in the formula and then converts it to a value: with .Offset(0, 1) .Formula _ = "=IF(OR(ISBLANK(" & .Address & ")," & .Address _ & "=""NA""),"""",VLOOKUP(" & .Address & "," & _ myLookupRng.Address(external:=True) & ",8,FALSE))" .value = .value end with Dallman Ross wrote: In , Dave Peterson spake thusly: tommylux wrote: How do I add to this code so that only the value of the formula is entered into the cell automatically, rather than the formula itself? For me (and probably lots of others), the older posts have aged off and the context of your follow up is lost. Here you go: In .com, tommylux spake thusly: 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 | |
|
|
![]() |
||||
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 |