Excel. formula inside formula?

Question

hi ,

i got one formula as below

=IF(OR(C21<>"",(AND(C22<>"",C21<>"")))...

and at cell A1 got formula to count somethign and let say the A1 result is 4142

how do i make the C(refer to value A1)?

C4142

C(??? refer to A1)

Answer 1

your formule got cutoff, but your basic question is

C(??? refer to A1)

for that you would use the indirect() formula

copy&amp;paste this into a blank cell

=INDIRECT(&quot;C&quot;&amp;A1)

now when you change cell A1 to 4142

then the formula will see that you want cell C4142

and whenever you change A1, then the formula will replace the 4142 with the number of your choice.

read source websites for more details

edit-

if you start in column C then use this formula

=INDIRECT(CHAR(COLUMN()+64)&amp;$A$1)

you can adjust the 64 to whatever start number you want...because remember

=char(67) will give you the letter C

and 64+3rd column is 67, and that sounds like where you are starting.

now you should be able to drag (your formula inside a formula inside another formula) across.

edit again-

ok that will get you 26 columns

if you have more then 26, then you have to use the R1C1 cell refernce style.

=INDIRECT(&quot;R&quot;&amp;$A$1&amp;&quot;C&quot;&amp;COLUMN(),FALSE)

then drag that across