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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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