Results 1 to 4 of 4

Thread: Vlookup doesn't yet exist

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup doesn't yet exist

    Hi guys;

    I have a excel workbook, that holds a number of worksheet templates, which I call my Control_Workbook

    I have another Workbook called - Device_Workbook
    Device workbook has a permanent sheet called Ip_Adresses.

    My Control_book copies a sheet into my Device_workbook called "Devices" using a VBa script which works fine

    Now there's a formula in Devices it needs to do a Vlookup, but the table array is in Ip_address.

    If I try to add that to my Control book (Ip adresses doesn't yet exist here, it won't be accessible, until I copy this sheet into the Device workbook if you understand?)
    So it trys to open the browser window, as if it's trying to find the sheet. So I can cancel that, and fine, it accepts the formula.

    Trouble is the formula isn't dynamic when it gets copied over. It doesn't do anything.

    Sometimes if I select the cell and press enter it starts working, or sometimes I need to re browse the path of the table array then it works.

    Help!

  2. #2
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup doesn't yet exist

    The easiest solution I can think of would be to have a blank sheet in Control_Workbook called Ip_address and instead of copying the Ip_address sheet, just copy the data from the permanent copy in Device_Workbook into the sheet in Control_Workbook.

    Another possible solution would be to have the formulas stored as text (by preceeding them with an apostrophe) ... for example '=VLOOKUP(...)
    And then replacing the apostrophes with nothing after the Ip_address sheet has been added.

    I would personally lean towards the first option as it is quite a bit more
    Last edited by BiocideJ; Sep 17th, 2019 at 03:41 PM.
    I use Excel 365.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup doesn't yet exist

    Thanks BiocideJ, do you know, just as I was writing that post earlier I was thinking of the first solution myself. Just hide it in the worksheet. Isn't there a way to make it "really hidden and I wonder would that matter?
    Last edited by Kevineamon; Sep 17th, 2019 at 05:40 PM.

  4. #4
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup doesn't yet exist

    Quote Originally Posted by Kevineamon View Post
    Isn't there a way to make it "really hidden and I wonder would that matter?
    Yes, programattically you can set the worksheet.Visible property to 2 (xlSheetVeryHidden) which basically just means the only way to UNHIDE the sheet is via VBA.

    Ultimately, whether or not it matters just depends on how much you are worried about someone un-hiding the sheet.
    I use Excel 365.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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