VLookup

[D Willett]

The following:

=IFERROR(VLOOKUP(Master!C3,Admin!J:K,2,FALSE),"")

Uses the sheet "Master" in the formula.Can this be changed to use "ThisWorksheet" or whatever sheet the formula is on?
So if the worksheet is duplicated, copied the formula looks on the current worksheet.

Cheers

[HansV]

Simply omit the reference to the worksheet:

=IFERROR(VLOOKUP(C3,Admin!J:K,2,FALSE),"")

[D Willett]

Doooohhhhh !!!

Cheers Hans

[D Willett]

Its not a good day today...

In one cell, I'm adding the formula as above and I see a result instead of the formula.
I've checked the properties of the column cells, they are "General" and have selected the "Show Formulas" from the ribbon.
All I see once I've entered the formula is the result, if I copy the cell down using the handle the result shows in all cells..
Once I enter the formula in the cell and press return the result shows, when I select the cell again and expect to see the formula in the formula bar I only see the result.

If I enter the formula in a new column or cell it is fine.
Is there a setting preventing me from fixing this?

[HansV]

If you have turned on "Show Formulas", you'll see formulas instead of results of course.
If a cell has been formatted as Text, anything you enter into it will be displayed as entered, including formulas.
If a formula spontaneously changes to its result, there might be code that causes this problem, or an add-in.

[D Willett]

Got it, its this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("G3:G23")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

As the G column is the problem!!
Pheww !!!!!

Thanks

[HansV]

A ha! Good that you found it.