Vlookup doesn't yet exist

[Kevineamon]

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!

 

[BiocideJ]

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

 

[Kevineamon]

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?

 

[BiocideJ]

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.