Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Need help with cell content

    Hello there,

    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.

    Hope that you can understand me.

    Thank you,
    Dev.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Dev,

    If you could post a sample of your vlookup, which columns would be hidden, which values will be automatically filled, and where you want the new value to be placed, it would be most helpful

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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?

    Steve

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi guys.

    @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 ).

    Thank you,
    Dev.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    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]

    zeddy

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Values in this cell will be filled automatically from other program
    Dev,

    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
    Attached Files Attached Files

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    dev11111 (2014-10-15)

  8. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you Maudibe. You are great. Dont know what to say..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •