Select From Drop Down And Pull Data From Different Sheet in Microsoft Excel

In this article, you will learn how to extract data from different sheet using drop down list.

The drop down list will be helpful for the end user to select the item from the drop down list & get results based on the selection. We will use INDIRECT function to get the output.


Let us understand with an example:

  • We have Revenue calculated for each month & the idea is to show the Revenue collected for the selected month in Summary sheet.



  • Cell B1 contains dropdown list of all the months in this workbook & based on the selection in the drop down list, cell B3 will get automatically updated.

Following is a snapshot of Jan Month data:




  • In summary sheet, the formula would be =Jan!B7




  • After taking the reference from Jan month sheet, we will use Indirect function & the formula is =INDIRECT ("Jan!B7")



  • The last step of this exercise is to make the reference (Jan in our example or anything before!) need to be replaced with “&B1&”




  • Now the formula becomes =INDIRECT(""&B1&"!B7")



In this way, you can create dynamic dropdown lists which will pull the data from different sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube