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.

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.

Users are saying about us...

  1. Jeg har denne formel =INDIREKTE(""&C2&"!C31") og det virker ikke.
    C2 er den celle hvor drop down listen er og !C31 er resten af den henvisning der var før, den så således ud før
    =Kontoplan!C31, (giver resultat "Udgift") så blev det sådan
    =INDIREKTE("Kontoplan!C31") (Giver stadig resultat "Udgift") og til sidst således
    =INDIREKTE(""&C2&"!C31") (giver nu resultat "#REFERENCE!")

    • Hej Johnny,
      Jeg forstår ikke fuldstændigt problemet, men denne artikel kan muligvis hjælpe dig. Hvis det ikke hjælper, skal du forklare, hvad du vil gøre.

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