SOLVED

Data Pulling - One Excel Sheet to Another From Dropdown List

Copper Contributor

Hello all!

I am relatively new to Excel and I need help trying to pull data from one file to another using a drop-down list. Any help would be much appreciated! 

I know how to create drop-down lists, and I am able to index/pull-data from one sheet to another. The problem I am facing is that the syntax that I use for pulling data from one sheet to another is not the same need to pull from one data file to another file. I have not been able to figure out the syntax.  

I will try to explain the best I can what I am trying to do below: 
"Employee1" - This is a file in which I would like Employee 1 to input some of his/her put string data/notes per month. It contains January through December sheets.  
"MasterFile" - From this file I would like to be able to access Employee 1's data and any other employee I choose from a dropdown list (there are about 150 employees). The droplist contains a list of employee file names and the month I would like to review. 

For example, I have been using the following sytax to access data from the Employee1 file to the MasterFile: '[Employee1.xlsx]January'!E1 (let's call this Formula 1)<-- This works but I have to manually input the month and file name. 

What I need is to make "Employee1.xlsx" and "January" change in Formula 1 depending on my employee and month selection from the dropdown list in the MasterFile.

The employees dropdown list is in cell E5 and month in E6, so I thought I could just type '[E5]E6'!E1
but that did not work. Then I learned about the INDIRECT() function -- the syntax in the videos I saw on YouTube were easy to follow but they did not deal with file-to-file data transfer, they were only sheet-to-sheet. 

Any ideas on how to get this working would be very appreciated, thank you all in advance!

1 Reply
best response confirmed by MicroSoft1040 (Copper Contributor)
Solution

@MicroSoft1040 

INDIRECT() works on another file, but only if such file is opened. Don't think you keep opened all 150 files.

An option could be to collect data from all files into the master sheet in another file by Power Query, and here you may sort, filter, whatever such master data.

1 best response

Accepted Solutions
best response confirmed by MicroSoft1040 (Copper Contributor)
Solution

@MicroSoft1040 

INDIRECT() works on another file, but only if such file is opened. Don't think you keep opened all 150 files.

An option could be to collect data from all files into the master sheet in another file by Power Query, and here you may sort, filter, whatever such master data.

View solution in original post