Google-sheets – Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryimport

What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list?

Best Answer

=ARRAYFORMULA(SUM(HLOOKUP(C1, 'LB dinamic'!A1:H, 
 {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}, 0)))

=SUM(INDIRECT("'LB dinamic'!"&
 ADDRESS(2,                      MATCH(C1, 'LB dinamic'!A1:1, 0), 4)&":"&
 ADDRESS(ROWS('LB dinamic'!A:A), MATCH(C1, 'LB dinamic'!A1:1, 0), 4)))

=SUMPRODUCT(QUERY(TRANSPOSE('LB dinamic'!A1:M), "where Col1='"&C1&"'", 0))

First Step

is to set the drop down menu's (combo box) on the range you want. In my example it will be in sheet1, having range A1:A10. From menu Data, choose this option:

enter image description here

Set the criteria range, to be the range in sheet2 (values in combo box):

enter image description here

Now the values will appear in the first sheet:

enter image description here

Second Step

is to add the following formula in cell B1 of sheet1:

=ARRAYFORMULA(IF(ISBLANK(A:A)=TRUE,"";VLOOKUP(A:A,Sheet2!A:B,
  {2} * SIGN(ROW(A:A)),0)))

This will automatically populate column B, with the values found in column B of sheet2, corresponding to the input given in column A of sheet1.

See the example file I've prepared: COMBOBOX and VLOOKUP

Google-sheets – Populate column with editable range based on drop-down

Instead of using drop-downs from data validation and formulas put all the information in a single sheet and use filters or filters views. For details see the official help article Filter your data - Docs editors Help

Best Answer

Related Solutions

Google-sheets – Change value of a cell based on a drop menu of values on a different sheet

First Step

Second Step

Google-sheets – Populate column with editable range based on drop-down

Related Topic