Need help with cell content

I need help with one problem. I'm using VLOOKUP function in my excel and would like to find other solution that will solve my problem. So here it goes:

There is one column which is used as VLOOKUP array. Problem is, I dont want to see that column. Here is idea what i would like to do (if possible):

- I would like to VLOOKUP array column to be hidden. Values in this cell will be filled automatically from other program so there is no need for user to see it. But, I would like to open new column which will have only one row. In this column there will be copied last value which is inserted in VLOOKUP array (hidden) column. Every time when new value is inserted, this value will be copied in this column.

The easiest way may be for whatever code you are using in the other programs to automatically fill in the hidden column, to also replace the value in the row of the other column. How is that being done?

@Maudibe
Thats the same excel u already helped me with earlier . I uploaded it and added comments so it should be much more clearer what i would like to do.

@Steve
I will use this option if there is not possibility to do this in excel. I would like to have as much as possible automated excel (+ I didnt work with excel earlier with VBA so im corious how strong it is ).

I'm not sure if I understand your question, but a formula in say, cell [D1] , to return the last entry in a column could be as simple as:
=INDEX(B:B,COUNTA(B:B))
..which will return the last entry in column [B]
assuming there are no gaps in the entries in column [B]

Placing this code in the sheet's module will take any value placed in column B, whether manually or programmatically placed there, and enter it into cell D1. Your Vlookup formula will function independently of this code. I have placed some sample code with a button that will programmatically place values in column B so you can see the effect.

The only way it does not work is if a cell in column B has a formula to get its value. For example, Cell B6 has the formula =E1. If you enter "hello" in E1, "hello" will also be in B6 but it will not trigger the event. However, this is not the process of adding values to column B that you describe.

HTH,
Maud

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
[d1] = Target.Value
Application.EnableEvents = True
End If
End Sub