Excel 2007 : Cell with no formula referencing to rows in another sheet. How?

[srdalxMI]

Hi,

I have Sheet1 called DATA and sheet2 called CERTIFICATE.

In DATA, you guess, I have a bunch of data and in CERTIFICATE is, you guess, a certificate where specific data from DATA sheet is shown. Certificat is using =INDEX formula for the reference.
All the cells in the CERTIFICATE sheet that have =INDEX formula reference to a specific column and that is fixed part. Dynamic part is the row reference. It is made in a way that all formulas, for example. =+INDEX(Column name;$F$6), reffer to a F6 cell!!! which is in a CERTIFICATE sheet. F6 cell is a dynamic cell with no formula and that is where i get VERY confused. In that cell I just write a number and automaticlly all the formulas in CERTIFICATE sheet change their data according to the number of a row that I write in F6 cell and the fixed column that is in the formula.

MY QUESTION is - how does that cell F6 work??? it has no formula in it...it is just a cell where i put a number of a row and automaticly all the formulas that reffer to that cell change accordingly to the data from DATA sheet for the specified row.

I am not advanced in excel so could you PLEASE help me with this cause it is very important that I understand it. I didn't program those sheets so that's why I don't know this.

Thank you very much for any help. And if you need some extra information, just write.

[teylyn]

Hello,

the description above is very hard to follow. Why don't you post a simplified sample workbook and explain in context?

As per Index(): the Index function returns a cell at the intersection of row and column

=Index(A1:H10,4,4)

will return the value of cell D4. If the first parameter is a one-dimensional range, like only one column or one row, you can omit the last parameter.

=index(A:A,4)

will return the value of A4

=index(A:A,$F$6)

will return the value of column Ax, where "x" is the number value in F6.