Help with auto populating cells.

[Guest]

I have some cells you can select using a dropdown list using conditional
formatting, and I want to be able to populae the next cell depending on what
was selected but I don't know how to do it.

For example, if 1 is selected on cell A1 on the dropdown list, I want cell
B1 to automatically fill in ONE based on another list in the same workbook on
a different sheet...But I don't know how to do this - can anyone help me out
here? Thanks.

 

[Pete_UK]

Assuming that you have a table somewhere (say columns M and N)
comprising:

1 ONE
2 TWO
3 THREE

etc., let's say up to row 10, then in B1 you can use this formula;

=VLOOKUP(A1,M$1:N$10,2,0)

The drop-down in cell A1 (I presume you mean by Data Validation) can
use M1:M10 as the source list, so that it must be found in the table.

Hope this helps.

Pete

 

[Gord Dibben]

First of all the dropdown list is created using Data Validation, not CF.

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[MartinW]

Hi Brad,

Check out this site.
Look under D for Data Validation - Dependant Dropdown
http://www.contextures.com/tiptech.html

HTH
Martin

 

[Guest]

Brad,

Assuming, you have the data to be selected (when drop down list is operated)
is in Sheet 2, say in range D37. The data based on which you have created
the drop down list, let us assume, are in range B4:B8 of Sheet 1. Then,

in the cell next to the drop down list ( on Sheet 1), type the following and
try:

=INDEX(Sheet2!D37,MATCH(Sheet1!D3,Sheet1!B4:B8,0))

If A1 is going to have only Serial Nos., say 1-5 or 1-10 and so on,
representing the row numbers of the range in sheet 2 and you are required to
retrieve data in the same order, you can simply type A1 in place of
Match(Sheet1!B4:B8,0), as the number will serve as the row number for the
Index function.

Best Wishes