How do I reference the cell to the left of the current cell in Google sheets?

Question

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

Solution 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.

Solution 2

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

Solution 3

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)