Vlookup doesn't yet exist

Kevineamon

New Member
Joined
Aug 1, 2018
Messages
27
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top