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.
In this article, we will use INDIRECT function to get the output.
INDIRECT: Returns the reference specified by a text string.
ref_text is a reference to a cell. If ref_text is not a valid cell reference, then INDIRECT function will return #REF error.
The second argument A1 refers to a logical value that specifies type of reference is contained in the cell ref_text.
If a1 is TRUE or omitted then ref_text is determined as A1-style reference.
If a1 is FALSE then ref_text is determined as R1C1 style reference.
Example: If value in cell A1 contains 10, B1 contains A1 & we use INDIRECT function in cell C1=INDIRECT(B1), then result would be 10
Let us take 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.