CODEWITHSUNDEEP
google-sheetsformulaspreadsheet
Thales454
Thales454

Reputation: 19

SPREADSHEET INDIRECT() with variable column

If A1 has a value of 2, =INDIRECT("L"&(5+A1)) will return me cell L7's content. And my A1 value keeps on being updated.

But I need a way to sort through columns instead of rows, like L7, M7, N7, O7 and so on...

Which formula can help me sort through the columns? I also know that =COLUMN() returns the current column converted to number, but I had no luck with =INDIRECT((column()+A1)&7).

Upvotes: 1

Views: 2000

Answers (1)

JPV
JPV

Reputation: 27292

You can use the address() function inside indirect. Address() returns a cell reference as a string. So for example, if you enter in G5

=indirect(address(A1, column()))

while A1 is 1 the above formula will return the contents of cell G1. See if this helps?

Upvotes: 3

Related Questions