CODEWITHSUNDEEP
google-sheetsformulas
Swogget
Swogget

Reputation: 325

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

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

Upvotes: 14

Views: 48040

Answers (3)

DanielM
DanielM

Reputation: 4033

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

Upvotes: 23

Kpym
Kpym

Reputation: 4023

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)

Upvotes: 9

Wicket
Wicket

Reputation: 38131

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.

Upvotes: 1

Related Questions