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
 
img1
 
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.
 
img2
 
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
 
img3
 
In summary sheet, the formula would be =Jan!B7
 
img4
 
After taking the reference from Jan month sheet, we will use Indirect function & the formula is =INDIRECT (“Jan!B7″)
 
img5
 
The last step of this exercise is to make the reference (Jan in our example or anything before!) need to be replaced with “&B1&”
 
img6
 

img7
 
Now the formula becomes =INDIRECT(“”&B1&”!B7″)
 
img8
 
In this way, you can create dynamic dropdown lists which will pull the data from different sheets.
 
 



Example:


One thought on “Select From Drop Down And Pull Data From Different Sheetin Microsoft Excel 2010

  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>