# Populate Data (Based on Drop Down Selection)

1. ## Populate Data (Based on Drop Down Selection)

Can anyone assist in helping me with the following? I have a dropdown box and when a user selects an account from this box i would like the data from the orange box (another spreadsheete consisting of account forecasts & shipments for a particular product) to populate in the yellow fields for the product (depending on the account selection). Hopefully this make sense, not sure what type of formula i should use, thoughts? Appreciate any help...

2. ## Re: Populate Data (Based on Drop Down Selection)

If you are using the combo box, change the cell link to a single cell, like \$I\$3

then use formula:

=IF(\$I\$3=1,"",SUMIFS(K\$4:K\$8,\$I\$4:\$I\$8,INDEX(\$S\$2:\$S\$4,\$I\$3-1),\$J\$4:\$J\$8,\$A5))

or, if you are using it in 2003 or earlier also....

=IF(\$I\$3=1,"",SUMPRODUCT(--(\$I\$4:\$I\$8=INDEX(\$S\$2:\$S\$4,\$I\$3-1)),--(\$J\$4:\$J\$8=\$A5),K\$4:K\$8))

3. ## Re: Populate Data (Based on Drop Down Selection)

Thanks for the response, why would i only select a single cell? Can you by chance clarify by looking at the excel file i attached...thanks again for your help, its appreciated!!

4. ## Re: Populate Data (Based on Drop Down Selection)

The cell link is the cell you want to send a numeric result that represents the position within the list that your choice is in... this way you can use that result to get do your search and compare.

5. ## Re: Populate Data (Based on Drop Down Selection)

Originally Posted by jkoste03
Thanks for the response, why would i only select a single cell? Can you by chance clarify by looking at the excel file i attached...thanks again for your help, its appreciated!!
In trying to ease down the load of your contributor. Let answer, the control cell link only needs to be once cell. In that cell it will return the respective position of your selection. For instance, I would change your input range to just \$S\$2:\$S\$4 rather than \$S\$1:\$S\$4 under the Format Control window. Therefore, if you choose Account C, that is on relative position 3, and it will return that on the assigned cell link.

I am not quite sure whether it matters to have a range, as I believe, that it will only modify your first cell in case you have specified a cell range. But to be on the safe side and to avoid possible issues, its best you limit it to just one cell.

6. ## Re: Populate Data (Based on Drop Down Selection)

Thanks for everyones help, will keep you posted if i don't figure it out.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1