Vlookup&Columns

[eacans]

I have a table that is linked via vlookup to another table which gets automatically populated.

The problem is that if I add new columns to the first table, the vlookup function fails and reads values from the wrong column.

e.g:

in table 1: first column gives me names. second column gives me age.

so for table2 using vlookup, i can get the entry in column 2 of table 1 which is age.

but if i add another column between the two existing columns of table 1, call is County column, table 2 instead of giving me age will now read of country.

is there any way I can make excel follow the column in table 1 even if new columns are added all around it?

 

[AlphaFrog]

In your Vlookup formulas you hard code the column number e.g.
=VLOOKUP(A1, Table1, 4, 0)
This returns the lookup from the 4th column in Table1

If you use a cell reference within the lookup table to determine the column, that cell reference will then change when you insert new columns.
=VLOOKUP(A1, Table1, Column(Sheet2!D1), 0).
This references the 4th column (column D equals 4).

In this example, if you were to insert a new column before Sheet2 column D, then column D becomes column E and the lookup columnn will be column 5.

 

[eacans]

thanks a lot :D

 

[eacans]

One more question:

some blank cells are linked up to another sheet. but in this other sheet, the blank cells are displayed not as blanks but as zeros (0).

is there some way i can display blank cells as blank cells.

 

[AlphaFrog]

You can use an IF function to test if your VLOOKUP returns a blank or not.
=IF(Vlookup formula here = "", "", Vlookup formula here)

 

[eacans]

thanks a lot again.

One more isse:

I used a simple macro pasted below. It works fine, just that I want to remove the screen movements it shows during the filter,copy and paste process. make it seem more professional. can you advise on what modifications i need to use to the coding?

Sub Macro1()
'
' Macro1 Macro
' Filter
'
'
Range("A3:A2500").Select
ActiveWorkbook.Worksheets("c. Prospect List").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("c. Prospect List").Sort.SortFields.Add Key:=Range( _
"A3:A2500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("c. Prospect List").Sort
.SetRange Range("A2:A2500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
Range("A3:A2500").Select
Selection.Copy
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[AlphaFrog]

Sub Macro1()
    Application.ScreenUpdating = False
    Range("A3:A2500").Select
    ActiveWorkbook.Worksheets("c. Prospect List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("c. Prospect List").Sort.SortFields.Add _
        Key:=Range("A3:A2500"), SortOn:=xlSortOnValues, _
        Order:=xlDescending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("c. Prospect List").Sort
        .SetRange Range("A2:A2500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

Sub Macro2()
    Range("H3:H2500").Value = Range("A3:A2500").Value
End Sub