I have sheets each with an employee name. I have a Summary Sheet that gets the weekly total from each employee sheet using VLOOKUP.
On the summary sheet, I have a drop down cell with all employees names. I am trying to create the VLOOKUP to go to a the sheet that is selected in the drop down.
=VLOOKUP(A2,[Employee]!A2:N20,14,FALSE)A2 is getting the date on the summary sheet and going to the employee sheet for the total for that week.
[Employee] is the part that I would like it to reference a drop down cell on the summary page.
I have tried DIRECT and INDIRECT but I am very new to spreadsheets.
11 Answer
If I understand what you want correctly, you want to have a Summary sheet that allows a user to choose an Employee sheet name in a dropdown cell and then lookup cell contents based on that selection.
I will do this using VLOOKUP, but if you are new to spreadsheets, you probably don't know that this is an old function that sometimes leads to problems and is not considered a best practice. Do a search on XLOOKUP (if you have Excel 2016+ or Excel 365) or "INDEX MATCH" and you will see a de rigueur approach that will avoid later headaches should you redesign your employee worksheets.
Here is a view of the worksheets that I created to simulate your question:
There are the Summary and Employee sheets that you refer to as well as a sheet that I created called namedRanges which may not be needed for your case, but I find it easier and cleaner to put content for dropdowns and other global references onto a sheet called namedRanges - it makes them easier to find and change. In namedRanges, I created the content of your dropdown list of sheets. I made the list starting in A1 with the name that I will use for that list, so that it is easier to read and diagnose later:
NB: These must be exactly as they are on the worksheet name. There is a way to ensure that they do match even if you later change the worksheet name, but that is getting away from your question, so I will put that method in the notes at the end.
Also, I left out emp5 on purpose as will be discussed later.
We could stop here, but to make things easier to manage later, we will create a named range for this dropdown list. To do that, go to the Formulas tab and choose Define Name. I named my range _EmployeeSheetnames and in the Refers to: field I entered this formula that dynamically reads the list of sheets as they are changed: =$A$2:INDEX($A:$A,COUNTA($A:$A),1)
Now you are all set implement the solution. In summary, I applied a Data Validation under the Data tab with List validation =_EmployeeSheetNames.
You could also put the =$A$2:INDEX($A:$A,COUNTA($A:$A),1) formula here, but giving this a name makes it easier to reuse, trace and manage later.
Now B1 in summary has the dropdown that you wanted - you might want to name that cell as well in order to make everything else more readable, but I will continue without naming it.
In cell B2, you can put in the vlookup that you wanted like this:
=VLOOKUP( A2, INDIRECT( "'" & $B$1 & "'!A1:N20" ), 14 )and copy that down to the rest of your cells in column B. If I have understood your objective, then this would address it, but let's address some other points:
Employee Sheet Discovery
I cannot think of a way to dynamically discover new employee sheets as they are created without VBA. I have tried some techniques, but they have never worked and if someone has a technique, then it would be great to know it. So you must manually add employees to the list in namedRanges. So, because I left out emp5, it is not in the dropdown. If you add emp5 to the list, it will automatically be added to the dropdown.
XLOOKUP Alternative
The problem with VLOOKUP is that if the data you are trying to process moves from column N to another column, the hard coded 14 will cause VLOOKUP to look at the wrong data and you might not even know that is happening. You would be lucky if it throws an error, but you could be really unlucky if it does not throw an error because it would be silently giving you garbage. XLOOKUP (and the INDEX MATCH method) avoid this problem. An alternative form of the VLOOKUP above would be:
=XLOOKUP( A2, INDIRECT( "'" & $B$1 & "'!A1:A20" ), INDIRECT( "'" & $B$1 & "'!N1:N20" ) )VOLATILITY
NB: INDIRECT is a volatile function. It recalculates every time you enter data or press F9 to calculate. If you have a lot of other cells that depend on the cells that contain INDIRECT, they will also recalculate, so you could end up with a spreadsheet that has frustrating slow response times. Most of the time, this is not a problem, but if you find that each time someone enters data in a cell, it takes 1-2 seconds to respond, the problem is INDIRECT. I know of no other way to do what you want than to use INDIRECT.
Dynamic Sheetnames
So, the sheet names are hand-typed in this example and they must exactly match the employee sheet names. If there is a chance that someone will change an employee sheet name later, you will have to update your _EmployeeSheetNames list. A way to do this automatically is to create a named range on each employee worksheet that gets the sheet name. Go to the employee sheet that you want to name and then go to the Formulas tab and select Define Name. Create a named range called _sheetName with this formula:
=MID( CELL("filename", 'emp1'!$A$1 ), FIND( "]", CELL("filename", 'emp1'!$A$1 ) )+1, 9999)
You must change the scope to the worksheet. It will default to workbook. This is important because you want to have one instance of _sheetName for each employee sheet. Now do the same for each employee worksheet. You can speed this up by using Name Manager in the Formulas tab.
Once you have done this, then you can go back to your _EmployeeSheetNames list and in each entry change it from text (like "emp5") to the formula ='emp5'!_sheetName. It ensures that you have the correct name and it also ensures that if the worksheet name is ever changed, your dropdown list will know it.
NB: this method is also volatile because it uses the CELL function, but it is only used by your INDIRECT which is already volatile, so this will not contribute anything new to calc times.
