12

I have to apply a function to every cell in column 2, which retrieves information about a symbol in column 1. I have 100 columns and don't want to individually plug each symbol in, so I need a way to reference the information in A2 in the function being used in B2

E.g. This is the spreadsheet I'm working on.

=GOOGLEFINANCE(SymbolDisplayedInTheCellToTheLeftOfThisCell, "price")

Thanks

Swogget
  • 185
  • 1
  • 2
  • 9

3 Answers3

23

While Ruben's answer is better for most calculation (and copy-paste should do the change for you). To get the cell to the left you can use ADDRESS function and ROW and COLUMN

If you write =ADDRESS(ROW(), COLUMN()-1, 4) on B4 for example, it will return A4

Then you can use INDIRECT to get the value in that cell

Federico
  • 3,650
  • 4
  • 32
  • 45
DanielM
  • 3,598
  • 5
  • 37
  • 53
  • Thanks, works perfectly. This is the function that worked; =(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(), COLUMN()-1, 4)), "price")) – Swogget Nov 30 '17 at 21:11
  • Note that this is heavy compute compared to the simple copy/paste – DanielM Nov 30 '17 at 21:37
  • This is great, we use it to push entries to Google Sheets via an API (Custom Webhook in Zapier) and since hardcoding a cell reference value (e.g. B3) won't work because we don't know which row it will be in, this solves our problem. In our request in JSON format we just send it in string format: "=IMAGE(INDIRECT(ADDRESS(ROW(), COLUMN()+1, 4)))" and Google Sheets resolves it. In this particular case, this displays an image and gets the image URL from the cell right to it. – Paul Strobel Mar 10 '21 at 10:53
8

This is an old question, but if somebody needs it ....

=GOOGLEFINANCE(INDIRECT("RC[-1]",FALSE), "price")

The value on the left cell is obtained by

INDIRECT("RC[-1]",FALSE)
Kpym
  • 3,743
  • 1
  • 21
  • 18
1

I need a way to reference the information in A2 in the function being used in B2

Just write

=GOOGLEFINANCE(A2, "price")

Then select the cells below it and fill down. You could use "select and drag" of a combination of keyboard shortcuts:

  1. Change the active cell to a cell on the Column A: Left arrow
  2. Go to the last cell: Control+Down arrow
  3. Change the active cell to a cell on the Column B: Right arrow
  4. Select all the cells from the current active cell to the first non-blank cell (B2): Ctrl+Shift+Up arrow
  5. Fill down Ctrl+d

This works because A2 is a relative reference. The spreadsheet engine will automatically update the references when making a copy or thousands of copies of the formula.

NOTES:

  • GOOGLEFINANCE doesn't work with ARRAYFORMULA
  • Another alternative is to write a script, but the above procedure performance / learning curve is faster than Google Apps Script.
Rubén
  • 34,714
  • 9
  • 70
  • 166