Forum Discussion

camusement's avatar
camusement
Copper Contributor
Jun 30, 2022

Auto fill cells in column B depending on drop-down value column A

Hi guys,

 

I need to autofill the values (text input) in column B of my spreadsheet depending on what values (text input) are chosen in column A. Column A uses a drop-down list.

 

To put it more in detail: in colum A, I can choose between several options (Breakfast, lunch, dinner) from a drop-down list. When I choose e.g. "breakfast" in cell A1, I need to autofill cell B1 with the translation in French "Petit déjeuner". When I choose lunch in A2, B2 needs to autofill "lunch", A3 "dinner" so B3 "diner" etc.

 

I've tried linking cells, using formulas, ... I can't figure it out. I'd prefer not to use macros if possible.
The sheet is an online workbook, don't know if that changes anything.


Can anyone help me out?

excel
Formulas and Functions
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 30, 2022

    camusement 

    Create a translation table, on the same sheet or on another sheet:

     

     

    You can use the first column as Source for the data validation drop-down.

    Let's say you created the list in A1:B4 on Sheet2.

    The formula in B1 on the sheet with the dropdown could be

     

    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$4,2,FALSE),"")

     

    or if you have Microsoft 365 or Office 2021:

     

    =XLOOKUP(A2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$3,"")

     

    Fill down as far as you want.

Resources