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

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/tips/select-from-drop-down-and-pull-data-from-different-sheet-in-microsoft-excel.html
SHARE




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.

img1

 

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

 

img2

 

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

 

img3

 

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

img4

 

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

 

img5
img6

 

  • Now the formula becomes =INDIRECT(“”&B1&”!B7″)

img7

 

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

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>