Dynamic Cell Entry (2002 XP)

Topic

New Reply

I have in sheet1 a column of numbers that grows (downward) daily. The last cell entry is linked/referenced to sheet2. How do I make the last cell entry (only) in sheet1

Reply | Quote

Replies

If you’re saying that the last cell is a grand total (or similar), give it a name: Insert|Name|Define. Use that name as your Sheet2 reference – using + and then F5.

Reply | Quote

That is done but the last cell entry keeps changing and it would be necessary to update the name. Can the naming of the last cell be dynamic?
Hope I was clear if not let me know.
thanks

Reply | Quote

From what you’re saying, you would need a VBA procedure to derive the value. Is the location of the reference on Sheet2 always the same, or can it be pinpointed in a specific manner?

Reply | Quote

I don’t know what you mean by the naming being dynamic. But you can create a name that will refer to the last filled cell in a column.

Say that you have a number of contiguous entries (without gaps) in column A on Sheet1, starting with A1. Select Insert | Name | Define, specify a name, for instance LastInA, and set the Refers To box to the following formula:

=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0)

This name will refer to the last filled cell in column A. Is that what you mean?

Reply | Quote

Hans,
This is exactly what I was looking for, it works.

thanks again to all

Reply | Quote

A non-volatile, fast access to the last numeric value in column A…

=LOOKUP(9.99999999999999E+307,A:A)

Reply | Quote

This also works

thanks

Reply | Quote