Best setup for Referencing Data from a Master Workbook & Accessing the Data Model

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
37
Hello Mr. Excel Community!


I have a multifaceted question that first involves the best way to set up a master database and then reference it from other files, and then a more general question about accessing the Data Model to pull data into worksheets via user-friendly inputs (data validation dropdowns would be the goal, but open to suggestions). It is important to note that I will not be analyzing the data in the typical sense that is shown in all online examples where Pivot Tables are heavily used for dashboards and reports. I basically need lots of data validation dropdowns, whose selection is then used to lookup data in various tables for automating the creation of all kinds of forms, documents, contracts, etc.


Backstory:
I have a spreadsheet that has grown in scale and scope over the years to automate a lot of the manual input into one-off word and excel templates (my company just can’t seem to get enough of these unlinked forms). I have brought/created many of these into a single spreadsheet with the purpose of linking common information so that nothing needs entered more than once but will flow to all necessary forms, documents, etc. Let’s call it the Project workbook since it pretty much has everything I need to run a project in it. Each time I have a new project, I just copy the latest version and adjust the details with the relevant info for the new project. I gave up on trying to use external references to other workbooks many years ago because they always seemed to be problematic. So up until now, the Project workbook was a self-contained unit. However, I’ve recently been learning about how far Excel has come on the database front, so I decided it was time to move some of the tables in my Project workbook to a Master Database workbook. Additionally, my group has grown recently, and more than one person will now be using my Project workbook, so I figured it was time to centralize the common data. I consider myself an Excel formula superuser, but my skills on the databasing/ Power Pivot/ Pivot Table side are beginner level.


Current Master Database workbook setup:
I moved four Excel Tables that have common data for all projects into a workbook I am calling the Master Database, with each Table residing on it’s own worksheet: Vendors, Clients, Client Facilities, and Contacts. This allows for one central location to update this information as they grow or need revised, and additionally, all Project workbooks can get access to the latest updates by refreshing their links to Master Database. Right now, the data is just in four Excel tables, it is not linked to Data Model in the file. I have a unique ID column on all four tables, and that is used with INDEX/MATCH formulas to pull in related data between tables.


Current Project workbook setup:
I created the same four worksheets mentioned above and brought in the associated tables from the Master Database using: Get Data > From File > From Workbook. Then in the Navigator list, I chose the table (not the worksheet, not sure if this matters), then in “Load To…” I selected “Table”, “Existing worksheet =$A$1” and left “Add this data to the Data Model” unchecked. I did this on each of the 4 worksheets to pull in the tables (which I guess is done via Power Query.)


Note that both workbooks are located on two different subsites of my company’s SharePoint. Not sure if that matters or not but wanted to point it out.


Question 1: Master Database workbook
Should the data live in Excel Tables only, the Data Model Only, or both? Looking for the best/most efficient way to set this up.


Question 2: Project workbook
Should the data live in Excel Tables only, the Data Model Only, or both? I already have a lot of worksheets in this workbook and that get hidden/unhidden throughout various stages of a project. From a cleanliness standpoint, it would be great if the data only lived in the Data Model since the actual tables never really need to be accessed by the user as a whole (just various data that is pulled from it). However, I don’t know that I will be able to access the data how I want to if it’s not in an Excel table (see next question)


Question 3: Accessing the Data Model (more of a general question)
Can data be pulled from the Data Model into data validation dropdowns, without having to use Pivot Tables and Power Queries that need to be placed somewhere in the workbook? I have many dropdowns throughout the various worksheets, and if I needed a dedicated PivotTable or Power Query to feed each individual one, that might get messy. Right now with my data residing in Excel Tables in the Project workbook, I can easily create dynamic named ranges for the various columns of data that I want to be available to the users via data validation dropdowns throughout the numerous worksheets. I’d like to know if this same functionality can be achieved by pulling data directly from the Data Model, possibly by way of a CUBE formula (which I know very little about). Through hours of research on the cube functions, the only successful thing I was able to do was to look up a value in one column of a table in the Data Model and return another value from the same row in a different column. This is good, but the first step is providing a full list from various columns to the user for selection. Would love to know if this is possible or not.

To those who have taken the time to read this long-winded post, I greatly thank you!

Cheers,
Michael
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
IMHO,
- use Excel Tables and/or Power Query, and if necessary, standard worksheet functions
- use Data Model if you really need this (Pivot Table is the end point of the Data Model). Data Model increase file size
- too much text to read, sorry :p
 

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
37
Thank you!

So are you saying that there may be a way to reach my end goal without using named ranges and subsequent look-ups through INDEX/MATCH?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
sure, it all depends on your skills, computer, structure and amount of source data
 

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
37

ADVERTISEMENT

My Power BI skills are lacking, that's why I'm here lol.

However, every example I've come across using Power Query for dependent data validation dropdown lists has the queries living somewhere in the worksheet, and they end up only feeding a single set of dependant dropdowns, something like Region in the first dropdown, then Salesperson in the 2nd dropdown. So if I need 4 sets that have selectable options that are independent of what is selected for the other 3 dropdowns, then I need to have 4 separate queries taking up real estate in a worksheet somewhere, correct? Or are there more advanced ways to do this behind the scenes?
 

mc-lemons

New Member
Joined
Apr 30, 2012
Messages
37
Sorry, I thought Power BI was a catch-all term for Power Query, Power Pivot, etc since that's how this name of this sub-forum. I was just trying to say that I lack those skills/ knowledge across the board!

Well, it sounds like I may have everything set up as best as I can then. I was just trying to see if any of the newer databasing/data processing functionality in Excel that am I not very skilled in might offer a more modern and efficient solution.

For my question 3 though, does anyone know if it is possible to return the entire column of a table that lives only in the Data Model, directly into a delimited list without using a Pivot Table? It seems like if it were possible, it would be by utilizing the Cube function.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,128
Power BI contain Power Query, Power Pivot and some Charts - afaik, no chance back to Excel directly from Power BI

most of calculations/transformation you can do in DB then take results to Excel in needed

here all (I think) cube functions in Excel

you should know what, where, when and how to use adequate Excel features
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,570
Messages
5,548,837
Members
410,877
Latest member
RaeB
Top