Forum Discussion

Eirik Arntsen's avatar
Eirik Arntsen
Copper Contributor
Jun 08, 2018

Autofill cells based on one value of another cell

Hi,

 

Need some help to make my life a little bit easier:

 

I'm making plans for a production facility, and I want to autofill all the cells based on product number. I have all the data I need in another table.

 

Simplified, it looks like this:

 

 

 

If I write 1 in B1 then I want the rest of the numbers (persons needed, machine number and planned) appear automatically from the table below.

 

If anyone knows it will help me a lot!! Thanks

excel
Formulas and Functions
Tables

3 Replies

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 08, 2018

        Eirik,

         

        Better to transform your range with products data into the Table (stay on any cell inside and Ctrl+T), in this case you don't need to adjust your range size in formula.

        If your titles in column A are the same as table columns headers you may use

        MATCH(A2,Products[#Headers],0)

        instead of ROW()-1 (or sequentially numbers 2,3,4 which are numbers of columns in the table). In attached example I renamed the table on Products (Table tools->Design->most left in ribbon), final formula is

        =VLOOKUP($B$1,Products[#All],MATCH(A2,Products[#Headers],0))

         

Resources