google sheets
I have a Google Spreadsheet that I enter in new values everyday and want to have the equation run down the entire column so I don't have to drag the previous day's box down to copy the info.
It would work fine to drag the box all the way to the bottom of the sheet, but then the cells get filled with a 0.
How can I put the equation in the cells without having them give me a value right away?
i.e. only have them show a value when the rest of that row has info in it.
Use the following notation:
=AVERAGE(A$1:A5)
Copying this formula, will yield:
=AVERAGE(A$1:A7)
Absolute references explained:
Reference: Absolute reference
I will guide you to the solution:
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:
Set the criteria range, to be the range in sheet2 (values in combo box):
Now the values will appear in the first sheet:
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
Best Answer
From the menu:
#,##0_);"("#,##0")";"-"_)