How can I read the value of a cell directly into a cell reference?-CodePudding

In Google Spreadsheets, I want to read a cell containing the string 'e' into a formula which uses the 'e' as the horizontal component of a cell reference, eg 'e12'.

Hopefully clearer example:

a1 contains 'e'
a2 needs to contain 'e2'
then a2:a10 can contain 'eX' where X is 2 to 10.

I'm aware of the INDIRECT method, but I can't see how to use that to populate a2:a10 without a corresponding column of cells all containing 'e', one for each aX.

Perhaps I'm barking up the wrong tree.

I've got a table with list of names on the x axis and dates of meetings on the y axis. In each table element we have some string value to represent if they attended the meeting - eg "y" for attended the meeting "n" for did not, "a" for apologised for abscence in advance.

I also have a list, corresponding to the names on the x axis, of attendance, eg person1 has been to 60% of meetings. The trouble is that for every new meeting I insert a new column closest to the names list, and move all the other meetings one cell to the right. This means that for every new meeting, I have to update the formula with a new cell reference.

Before inserting a new meeting date, the formula looks roughly like DOSOMETHING(b2:y2). I then insert a column to the left of the b column. Google sheets cleverly updates the formula so to be DOSOMETHING(c2:z2), even though I really want the range to be b2:z2. I have tried DOSOMETHING($b2:y2), but inserting the column still causes this problem.

Thank you

CodePudding user response：

try like this in row 2:

=INDIRECT(A1&ROW()&":"&A1&10)

CodePudding user response：

instead of:

=DOSOMETHING(B2:Y2)

use this:

=DOSOMETHING(OFFSET($A2,0,1,1,99999))

the offset reference won't get messed up by the insertion of a column since it referrences column B by OFFSETing from column A. 99999 is just an arbitrarily large number that means "all the way to the right of the sheet.

you can read about offset here.