Find last entry in several worksheets

[Christien]

I've got a workbook that tracks all transactions for each customer of ours - there's a separate sheet within the book for each customer. Column A is strictly dates, and has a date entry for each transaction. I need to compile a list of dates of the last transaction for each customer, in a separate workbook. Is there a formula I can use that will scan column A from workbook A and take the data in the last cell with data and copy it to workbook B? It would be something like:

=('C:\customers\[transactions.xls]Smith'!Axx)

where the xx would be dynamic, in that it would update each time the workbook is opened, and would be "pastable" so that a different customer (i.e. Jones instead of Smith) would give different data.

Thanks!
Chris

 

[Oorang]

If Sheet "Mycustomer", column A contains your dates, then

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

will return the last entry.

 

[vane0326]

Here is an example.

List your worksheets.


1.)

Insert > Name > Define

Name:WSLST

Type this in Refers to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())

Click Ok


2.)

In cell A3 input formula and copy down.

=INDEX(WSLST,ROW()-1)

In cell B3 input formula and copy down.

=LOOKUP(9.99999999999999E+307,INDIRECT("'"&A3&"'!A:A"))

Book4
	A	B	C	D
1
2
3	Sheet2	26
4	Sheet3	120
5	#REF!
6
7
8
9
10
11
12
13
14
Sheet1

Hope it helps!

 

[Christien]

Thank you so much!!! With some massaging, that worked perfectly!

Chris