Select From Drop Down And Pull Data From Different Sheetin Microsoft Excel 2010

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.

Syntax: =INDIRECT(ref_text,A1)

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.

Users are saying about us...

  1. hi

    I have created a quotes spread sheet for hire of equipment, I have 3 individual worksheets for 3 separate peoples quotes, which include date, time, customer, description, and booked date, the 3 main worksheets, are ‘currently on hire, off-hired, and sales, i want to create a drop down box at the end of each row of information, I need the dropdown box to remove the data from the individual worksheets and move it to the main worksheets chosen in the dropdown box.

Leave a Reply

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

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>

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 Google PlusVisit Us On Youtube