Creating External Reference from 'Data Reference' Worksheet


New Member
Aug 12, 2015

I am trying to create an external reference linked to a DataSheet in another workbook.

Basically I have all my information and data in 2 worksheets based on a 'Data Reference' worksheet in the same workbook. This contains simply everything from named ranges used for Data Validation to INDEX / MATCH table sources. The actual Output worksheets use Macros as well as Data Validation and Index/ Match but are all linked to tables in this worksheet.

I wanted to be able to copy all this data into another workbook and keep this as the reference in the company database so that anyone can update it. This would hereby change the information in the original worksheet that is intended to be sent out to clients.

I thought of copying everything in the Data reference worksheet and then using the paste special and pasting it as a link in another workbook. This would then change the original Data reference and I would be able to hide the page and lock the workbook to keep clients from viewing or accidentally tampering with it.

This did not work and I have not been able to manage how to use a new source workbook to solve the issue.

Would I have to copy everything in the 'Data Reference' worksheet into another workbook and delete the original reference. Then Open a source workbook where all my original Data is linked. The point is just to be able to update the worksheets data after it has already been sent out to clients and users.

Thank You very much in Advance and please let me know if I haven't explained something correctly!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...