|Since I can't see your spreadsheet from where I'm sitting, it's not clear to me what you mean by "have as a result "C5" by having the number 5 in a cell."|
In any case, I'll explain it this way:
INDIRECT can be combined with other functions to create a cell reference that Excel can use. Just about any place where you could use C5 as a cell reference in a function, you could use the INDIRECT function to "build" a reference to C5 - or any other cell.
A1 = C
A2 = 5
A3 = A1 & A2 (displays: C5)
C5 = 25
=C5 * 2 would return 50, as would all of the following formulas:
=INDIRECT(A1 & A2) * 2
=INDIRECT(A3) * 2
=INDIRECT("C" & A2) * 2
=INDIRECT(A1 & 5) * 2
=SUM(C5, RIGHT(INDIRECT(A3), 1), INDIRECT(A1 & A2) - INDIRECT("A" & 2))
Posting Tip: Before posting Data or VBA Code, read this How-To.