Summary

[Guest]

I have a workbook with several sheets, one for each client. Each sheet shows
"Client Name", "Transaction Date", "Transaction Amount", "Balance". Now, I
wish to create a sheet with a "Summary" showing the "Client Name", "Balance"
for each sheet, and the "Summary" is based on "Today's Date".

How can I pick up the last balance of each sheet?

 

[Guest]

If there is only one row per date and the dates are in order. you could use
lookup

=lookup(datevalue("1/20/07"),'worksheet1'a1:a10000,'worksheet1'c1:c10000)
or

=lookup(today(),'worksheet1'a1:a10000,'worksheet1'c1:c10000)


where column a contains the date and column contains the balance you want.

 

[Guest]

Thanks Joel for the help. However, in my client sheets, the only time a date
is inserted is when there is a transacation. How will this work if there is
no date? I just want the "Summary" to pick up the last balance showing on
the client sheet.

 

[Guest]

I added an end after the last row of data and used a match function with an
offset

=OFFSET(A1,MATCH("end",Sheet1!A1:A23)-2,1,1,1)

Match will not look for an empty cell.

 

[Guest]

Hi Joel:

Thanks again. I sure appreciate your help. Unfortunately, I have never
used these functions before. Could you let me know which each cell address
is supposed to represent, so that I can change it for my workbook.

Thanks.

 

[David Biddulph]

Excel has a help facility which tells you the syntax of each Excel function
(with the strange exception of DATEDIF), and gives examples. I can
recommend its use, Dale.

 

[Guest]

The match will search colum a1:a23 for the word "end. Match will return the
cell location of where the "end" is located. Offset will move a certain
number of rows and column from the returned cell location

If end is at A20
row ofset = -1 will move to row 19
column offset of 1 will move to column b

the results will be the data at B19

Offset has 5 parameters in the following order
Reference cell - which will be the cell where the end was found
row Offset
column Offset
Number of rows - either leave out or set the default 1
Number of columns - either leave out or set the default 1

 

[Guest]

Thanks Joel...that is exactly what I needed. It works perfectly.