Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary function | New Users to Excel | |||
not sure which function to use for this summary | Excel Worksheet Functions | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
Summary | Excel Worksheet Functions |