If Cell has x then 10 other cells get automatically filled

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a form made in Excel that we want to have automatically filled in.

We have a drop down box in Cell A1 with 5 selections.

If we select choice 2 from the drop downs we want to automatically pull a
set of data from another worksheet and populate 10 different cells in the
form with the data pulled in.

If we select choice 5 from the drop downs we want to automatically pull a
set of data from another worksheet and populate 10 different cells in the
form with the data pulled in.
 
Hi Hardcourter,

On sheet 2 of the workbook, A1:A5 holds the same list as the drop down list
on sheet 1. The ten bits of data pretaining to each of the drop downs is in
cells B1 through K1, B2 through K2, and so on for all five on the list.

With your lookup selections in A1, select cells B1 through K1 on sheet 1 and
enter this formula in the highlighted cell (B1), and hit CTRL + SHIFT +
ENTER. Excel will enclose the entire formula with curly brackets { }.

=VLOOKUP(A1,Sheet2!A1:K5,{2,3,4,5,6,7,8,9,10,11},0)

Returns 10 bits of info for the drop down selection.

If the cells you want to populate are not all in a single row, but instead
are scattered about sheet 1, then each cell can have a VLOOKUP formula to
return the 10 bits of data for the A1 drop down selection. Something like:

In cell B4 =VLOOKUP(A1,Sheet2!A1:K5,2,0)
In cell D6 =VLOOKUP(A1,Sheet2!A1:K5,3,0)
In cell F9 =VLOOKUP(A1,Sheet2!A1:K5,4,0)

And so on until all 10 cells have formulas, each returning a single piece of
data for the lookup in sheet 1 A1.

HTH
Regards,
Howard
 
Back
Top