So I can calculate the column number based on a cell value as such
= CHAR(64+x)
with x the cell value based on e.g. user input
but now I want to add a value to another cell based on the calculated letter of the column from where to look for the value to add
say I calculated the letter B previously, based on e.g. user input
and I want to add the right value, namely the one in cell B3 (but this may change based on user input) to cell A1
=CHAR(64+2)3
does not work. Usually one uses =B3, but that is not possible here.
Any ideas?
To clarify Ben's comment, you could put an Index formula in any cell on a worksheet and it would return the cell based on row/column parameters. While it's probably not good to specify the entire worksheet, you could do that by having a formula of:
=INDEX(1:1048576,5,8)
or with a more limited scope/range:
=INDEX(A:Z,5,8)
Both of these would return cell H5 (row 5, column 8).
You could also use indirect (though Index is better). Something like this based on your formula:
=INDIRECT(CHAR(64+2)&"5",TRUE)
Which results in....
=INDIRECT("B5",TRUE)
This would return cell B5