use cell contents of a formula calculate cell reference

I'm having trouble following exactly what you want to do, but think maybe INDIRECT may help. More here.

SG

I suggest posting a screenshot.

there is a function called indirect() which takes a string which you can construct from the content of cells and then uses that string as a reference.

if you know how many cells are in a column and you want the value in the last cell of column C for the table named "Table 1" you can do this:

=indirect("C"&rows(C))

rows(C) returns the number of rows in column C (which would be the same no matter what column you used). I the table had 15 rows, then

C&"rows(C)"

would be

"C15"

indirect("C15") would return the value in cell C15

Hi Skip,

It may be possible, but several details will have to be supplied to come up with an answer to "How would it be possible to..."

COUNTA would give you the number of entries in a range of cells. How would tis relate to the "number of days" between the first day entry and the latest date (entry?)?

How would the 'range of cells' be determined?

What are the contents of the (referenced?) cell?

How would this content be used to 'update the information in the rest of the spreadsheet'?

If you want to retrieve the value from a cell in a specific column and the same row as the most recent date entered in another specific column, MATCH and INDEX may be a better pair of functions for this task.

Cell A2 of Table 2 (the smaller table) contains this formula:

=INDEX(Table 1::A:E,MATCH(MAX(Table 1::A),Table 1::A,0),MATCH("words",Table 1::1:1,0))

While this will not solve your issue, it will provide an initial step—getting the contents of the referenced cell (in this case E13 on Table 1).

What do you need to do with that content?

Regards,

Barry

A couple of ways to use INDEX, depending on your setup:

=INDEX(Table 1::E,MATCH(MAX(Table 1::A),Table 1::A,0))

=INDEX(Table 1::E,COUNTA(Table 1::A)+1)

SG

HI SG,

These examples, with views of the sorted table and with no repeated values in column E may make the differences in results from MATCH and COUNTA more clear.

Small tables contain:

MATCH and MATCH formula from my post above.

MATCH ans fixed column formula from your post above

COUNTA and fixed column formula from your post above

Sorts are done on column A of the larger table.

As entered:

Sorted ascending

Sorted descending

Your use of a fixed column and a one dimension range for INDEX is likely more efficient that the two dimension range and use of MATCH to determine the row and column values of the target cell used in my version.

Regards,

Barry