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.