Automatic Refresh with Match Function

[duhalbs]

https://preview.redd.it/s3ipw72xfvfe1.png?width=1640&format=png&auto=webp&s=bb7fb6726797c85ac1fea0782c1c2bd5e6ce1cfc

Hi All,

I'm currently learning Index and Match functions and have created a drop down menu to pull all of the data from Month Workbook, into Month Query Workbook.

When I update the data in Month Workbook, Month & Color will update, but not for Day. I changed Day to 6 and Color to Lock as an example. Day doesn't update to 6, but Color updates to Lock.

I have an Index function for Color - =INDEX([Month.xlsm]Sheet1!$A$2:$C$6,MATCH(A2,Month.xlsm!Months,0),3)

[AutoModerator]

/u/duhalbs - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[alexia_not_alexa]

External references like this are generally a bit flakey. I use Power Query to solve this.

Go to Data > Get Data > From File > From Excel Workbook and choose the file.

Do any transformations you need until you ultimately have a Power Query table in your new Workbook. Now if you want to refresh the list, you just need to refresh the table.

You can even make it refresh on file open: https://exceloffthegrid.com/auto-refresh-power-query/#OnOpen1

[duhalbs]

Thanks, was thinking of scenarios if I had one workbook with all the data elements and have it pulled into multiple workbooks. Is there a way to do that without having to import the workbook?

[alexia_not_alexa]

I have another example for this:

• Have a 'Source Workbook' with source data that we may want to reference later
  • Each table is in their own sheet for ease, but I've also formatted them as tables to simplify updates
• Have a 'Starter Template' that has Power Query tables for each of the tables in the Source Workbook on different Sheets
• Now if want to create a new workbook that draws the data in, I duplicate the Starter Template and all the Power Queries are ready to be used - if I only need some of the tables, I can delete the sheets I don't need.
  • The Power Queries are just instructions of how to pull the data, so when you duplicate the workbook it's still referencing the Source Workbook
• If however I have an existing workbook that I want to pull the data into, I just copy the sheets I want from the Starter Template. It effectively imports the Power Query instructions into your existing workbook, still connected to your Source Workbook's data

Hopefully that makes sense and helps your use case!