I have a cell which has to have a relative content based on another cell. For example: if cell A1 has value 4, cell B1 has to take its content from cell "=B(4+2)". In the similar manner, cell C1 has the column value based on the content of another cell. So if the A2 cell value is 6, C1 should take its value from C+6=I column and row 1, something like "=(C+A2)1". Would be great being able to use relative column and relative row!
Is it possible? I was expecting a syntax similar to "=B(1+4)" but doesn't work.
For your first question.
How to get "=B(4+2)"
=INDIRECT("B" & A1 + 2)
Your second question.
How to get "=(C+A2)1"
=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(C1)+A2,4),"1","") & 1)
The formula =SUBSTITUTE(ADDRESS(1,COLUMN(C1),4),"1","") is used to get the column letter. This gives you C. Here to get column I i.e. C+6 I wrote =SUBSTITUTE(ADDRESS(1,COLUMN(C1)+6,4),"1",""). So the second formula turns out be =INDIRECT(I & 1)