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
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
