Summary
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? |
Summary
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. "Dale" wrote: 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? |
Summary
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. "Joel" wrote: 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. "Dale" wrote: 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? |
Summary
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. "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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? |
Summary
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. "Joel" wrote: 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. "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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? |
Summary
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. -- David Biddulph "Dale" wrote in message ... 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. "Joel" wrote: 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. "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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? |
Summary
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 "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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? |
Summary
Thanks Joel...that is exactly what I needed. It works perfectly.
"Joel" wrote: 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 "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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. "Joel" wrote: 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. "Dale" wrote: 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? |
All times are GMT +1. The time now is 06:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com