indirect function

[WINDMILL]

I'm not familiar with why / when the INDIRECT function is used.
I've inherited a spreadsheet, it has 10+ spreadsheets, and throughout them
there are several different scenarios of the INDIRECT functionm,this is the
simple one =INDIRECT("c"&ROW()). Would someone please explain in laymans
terms the purpose of using this function.
Much appreciated.

 

[Teethless mama]

C1: cat
C2: dog
C3: chicken
C4: cow

your formula in D2

D2: =INDIRECT("C",ROW())
equivalent with =INDIRECT("C2")
It returns "dog"

 

[Dave Peterson]

=row() returns the row number for the cell with this formula in it.

So if =indirect("C"&row()) were in row 99, then it would evaluate to:

=indirect("C99")

=indirect() is a way you can use a string as a cell address.

="C99"
would return the characters: C99

=indirect("C99")
would go off to look at cell C99 and return the contents of that cell.

I don't know why the developer chose to use =indirect() in this workbook, but it
can be useful. If someone deletes columns A:E (with the formula in X99, say).
This formula will continue to work--it'll retrieve the value in C99 after that
deletion.