Google-sheets – How to link cell within googlefinance formula

formulasgoogle sheetsgoogle-finance

I'm trying to get the googlefinance formula to work for multiple country exchanges.

e.g. Tencent is listed on Hong Kong stock exchange, the code is 0700.

If I put =googlefinance(HKG:0700,"price"), it works, but I want to link the 0700 part to another cell, so it will look like =googlefinance(HKG:A2,"price").

But I can't type A2 physically, because it will not be treated as taking the content of cell A2.

Best Answer

Use string concatenation with &:

=googlefinance("HKG:"&A2,"price")

Note that I had to put '0700 in A2, or change the cell format to 'plain text'; just typing in 0700 will result in Google Sheets converting it automatically to 700.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.

Google-sheets – Conditional Filters Formula in Google Sheets

Seems like the answer was pretty easy. Managed to figure it out myself.

=FILTER(All!A2:C999, All!C2:C999 = IF(C4 = "Any", All!C2:C999, C4))

If C4 is set to Any, then the filter allow all of them since every cell will be equal to itself. If its not, it only passes the ones which are equal to the C4 value.

Best Answer

Related Solutions

Google Sheets – Create Link in Cell Using URL from Another Cell

Formula

Explained

Google-sheets – Conditional Filters Formula in Google Sheets

Related Topic