Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with auto populating cells.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with auto populating cells.
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 On Sep 29, 1:18 am, Brad wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with auto populating cells.
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 On Fri, 28 Sep 2007 17:18:02 -0700, Brad wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with auto populating cells.
Hi Brad,
Check out this site. Look under D for Data Validation - Dependant Dropdown http://www.contextures.com/tiptech.html HTH Martin "Brad" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with auto populating cells.
Brad,
Assuming, you have the data to be selected (when drop down list is operated) is in Sheet 2, say in range D3:D7. 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!D3:D7,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 "Brad" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting comma seperated from a single cell and auto populating in selected cells o | Excel Discussion (Misc queries) | |||
Auto populating date field | Excel Discussion (Misc queries) | |||
Auto populating fields | Excel Worksheet Functions | |||
Populating several cells | Excel Worksheet Functions | |||
Auto Populating cells from a master spreadsheet | Excel Discussion (Misc queries) |