Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
Hi,
I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B .....other data here ....spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ....spaces here 10/27/2006 30000 10/28/2006 35000 ....empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
Enter this anywhere, in a cell formatted to a date format of your choice:
=LOOKUP(2,1/((A1:A100<"")),A1:A100) And then simply copy it across to the next column to get the last value in Column B. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tfrentz" wrote in message ... Hi, I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B ....other data here ...spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ...spaces here 10/27/2006 30000 10/28/2006 35000 ...empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
Hi,
Thanks, that worked, but I guess I didn't explain myself completely as my intention was to be able to add data to the columns in the future. Can the formula's range be modified to handle the whole column? That way I won't have to manually adjust the ranges in the future. I tried making the range A:A, which no success. Also, could you explain what the parameters of the function mean? thanks "Ragdyer" wrote: Enter this anywhere, in a cell formatted to a date format of your choice: =LOOKUP(2,1/((A1:A100<"")),A1:A100) And then simply copy it across to the next column to get the last value in Column B. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tfrentz" wrote in message ... Hi, I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B ....other data here ...spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ...spaces here 10/27/2006 30000 10/28/2006 35000 ...empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
Vlookup will do what you want, as in:
=vlookup(999999999999,A:B,1) where 999999999 is greater than any value in column A =vlookup(999999999999,A:B,2) will pick up the adjacent column -- Regards, Fred "tfrentz" wrote in message ... Hi, Thanks, that worked, but I guess I didn't explain myself completely as my intention was to be able to add data to the columns in the future. Can the formula's range be modified to handle the whole column? That way I won't have to manually adjust the ranges in the future. I tried making the range A:A, which no success. Also, could you explain what the parameters of the function mean? thanks "Ragdyer" wrote: Enter this anywhere, in a cell formatted to a date format of your choice: =LOOKUP(2,1/((A1:A100<"")),A1:A100) And then simply copy it across to the next column to get the last value in Column B. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tfrentz" wrote in message ... Hi, I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B ....other data here ...spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ...spaces here 10/27/2006 30000 10/28/2006 35000 ...empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
Hi Fred,
That worked great. Thanks. "Fred Smith" wrote: Vlookup will do what you want, as in: =vlookup(999999999999,A:B,1) where 999999999 is greater than any value in column A =vlookup(999999999999,A:B,2) will pick up the adjacent column -- Regards, Fred "tfrentz" wrote in message ... Hi, Thanks, that worked, but I guess I didn't explain myself completely as my intention was to be able to add data to the columns in the future. Can the formula's range be modified to handle the whole column? That way I won't have to manually adjust the ranges in the future. I tried making the range A:A, which no success. Also, could you explain what the parameters of the function mean? thanks "Ragdyer" wrote: Enter this anywhere, in a cell formatted to a date format of your choice: =LOOKUP(2,1/((A1:A100<"")),A1:A100) And then simply copy it across to the next column to get the last value in Column B. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tfrentz" wrote in message ... Hi, I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B ....other data here ...spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ...spaces here 10/27/2006 30000 10/28/2006 35000 ...empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve last date and amount from columns?
To reference the entire column, you might try this:
=LOOKUP(99^99,A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tfrentz" wrote in message ... Hi Fred, That worked great. Thanks. "Fred Smith" wrote: Vlookup will do what you want, as in: =vlookup(999999999999,A:B,1) where 999999999 is greater than any value in column A =vlookup(999999999999,A:B,2) will pick up the adjacent column -- Regards, Fred "tfrentz" wrote in message ... Hi, Thanks, that worked, but I guess I didn't explain myself completely as my intention was to be able to add data to the columns in the future. Can the formula's range be modified to handle the whole column? That way I won't have to manually adjust the ranges in the future. I tried making the range A:A, which no success. Also, could you explain what the parameters of the function mean? thanks "Ragdyer" wrote: Enter this anywhere, in a cell formatted to a date format of your choice: =LOOKUP(2,1/((A1:A100<"")),A1:A100) And then simply copy it across to the next column to get the last value in Column B. -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "tfrentz" wrote in message ... Hi, I have a spreadsheet with dates and amounts in columns A and B. I want to retrieve the last date and amount in both columns right before the empty cells. The dates are in ascending order and there are spaces in some rows and some of the amounts can be spaces or zero. Here is an example Column A Column B ....other data here ...spaces here 01/01/2001 0 01/02/2001 5000 01/01/2002 10000 01/01/2003 20000 01/01/2004 25000 01/01/2005 0 ...spaces here 10/27/2006 30000 10/28/2006 35000 ...empty cells here I want to retrieve the the last row which contains the date of 10/28/2006 and the amount of 35000 into two cells adjacent to each other just like they are stored in column A and B of the table above. Any help greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
date formula | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
If function with date; if sale is Oct place sale $ amount here | Excel Worksheet Functions | |||
excel formula counting date to date in 4 columns | Excel Worksheet Functions |