Can I add to a cell reference?

C

Confused

Can anyone help? On one worksheet I have new data being filled in each month. On another worksheet I want to show the latest data only. How can this update itself each month, without having to manually change each reference (There are hundreds of them !)? I cannot seem to "add" to a cell reference

Any ideas? Thanks

Confused
 
A

A.W.J. Ales

Confused,

Take a look at the Offset function and/or Index function.
By using these functions, you can "add" to a cell reference.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Confused said:
Can anyone help? On one worksheet I have new data being filled in each
Click to expand...
month. On another worksheet I want to show the latest data only. How can
this update itself each month, without having to manually change each
reference (There are hundreds of them !)? I cannot seem to "add" to a cell
reference.
 
F

Frank Kabel

Hi
to get the last entry in a column use one of the following formulas:

0. if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))
 
Top