| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table lookup
I have a 3 Column Table: Begin Date, End Date, Interest. On a separate
worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
table lookup
Can the input begin date be earlier than the earliest begin date in your
table? For example, the earliest begin date in your table is 1-Jan (I assume that's 1/1/2009). Can your input begin date be earlier than 1/1/2009? -- Biff Microsoft Excel MVP "JeffK" wrote in message ... I have a 3 Column Table: Begin Date, End Date, Interest. On a separate worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
table lookup
Hi Jeff,
There may be a more elegant approach, but the following seems to work. In the Table page, create a helper column D that displays the row numbers (So, D2=ROW(D2), and fill down the column). In the Input page (I assume that the Begin and End dates are in B1 and B2 respectively). Enter the following formulas in A4, B4, and C4 respectively (The formulas assume that the Table page's name is Sheet1. If not, enter the sheet's actual name in place of "Sheet1" in every occurrence in the formulas. In A4, =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$8,4)=VLOOKUP($B$1 ,Sheet1!$A$2:$D$8,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $8,4)+ROW(A1)-1),"") In B4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,2,0)) In C4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,3,0)) Format Columns A and B for date, and Column C for percentage. Drag the formulas down as far as needed. Best regards, B. R. Ramachandran "JeffK" wrote: I have a 3 Column Table: Begin Date, End Date, Interest. On a separate worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
table lookup
I input the formula and adjusted the table/sheets to mirror your formula (to
get it working first and make adjustments later) Only the first 4 rows are copied then nothing. I'm guessing by looking at the first formula, the portion that read " +ROW(A1) " doesn't seem to reference to sheet1 and I think that's the reason I get only 4 rows of date (formula entered in A4 on the input sheet) Not sure how to fix. Tried but I get errors. "B. R.Ramachandran" wrote: Hi Jeff, There may be a more elegant approach, but the following seems to work. In the Table page, create a helper column D that displays the row numbers (So, D2=ROW(D2), and fill down the column). In the Input page (I assume that the Begin and End dates are in B1 and B2 respectively). Enter the following formulas in A4, B4, and C4 respectively (The formulas assume that the Table page's name is Sheet1. If not, enter the sheet's actual name in place of "Sheet1" in every occurrence in the formulas. In A4, =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$8,4)=VLOOKUP($B$1 ,Sheet1!$A$2:$D$8,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $8,4)+ROW(A1)-1),"") In B4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,2,0)) In C4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,3,0)) Format Columns A and B for date, and Column C for percentage. Drag the formulas down as far as needed. Best regards, B. R. Ramachandran "JeffK" wrote: I have a 3 Column Table: Begin Date, End Date, Interest. On a separate worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
table lookup
Hi Jeff,
I tested the formulas again, and they are working fine for me (I am using Excel 2007, but I think that it shouldn't matter). I even extended to Table to contain 10 more rows of Begin and End dates and percentages, and also varied the Begin and End dates in the Input page, to make sure that the formulas work; they do work fine. Also, the "ROW(A1)" parts in the formula don't have to refer to Sheet1 (ROW(A1) just generates the number 1, and is not sheet-specific). Please try again. I am cutting/pasting the formulas again from my Excel trial (This time the formulas contain the range A2:D18 instead of A2:D8 posted earlier to accommodate an extended Table. Change the range aqppropriately to suit to your worksheet. =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$18,4)=VLOOKUP($B$ 1,Sheet1!$A$2:$D$18,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $18,4)+ROW(A1)-1),"") =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,2,0)) =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,3,0)) Hope it works this time! Thanks, B. R. Ramachandran "JeffK" wrote: I input the formula and adjusted the table/sheets to mirror your formula (to get it working first and make adjustments later) Only the first 4 rows are copied then nothing. I'm guessing by looking at the first formula, the portion that read " +ROW(A1) " doesn't seem to reference to sheet1 and I think that's the reason I get only 4 rows of date (formula entered in A4 on the input sheet) Not sure how to fix. Tried but I get errors. "B. R.Ramachandran" wrote: Hi Jeff, There may be a more elegant approach, but the following seems to work. In the Table page, create a helper column D that displays the row numbers (So, D2=ROW(D2), and fill down the column). In the Input page (I assume that the Begin and End dates are in B1 and B2 respectively). Enter the following formulas in A4, B4, and C4 respectively (The formulas assume that the Table page's name is Sheet1. If not, enter the sheet's actual name in place of "Sheet1" in every occurrence in the formulas. In A4, =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$8,4)=VLOOKUP($B$1 ,Sheet1!$A$2:$D$8,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $8,4)+ROW(A1)-1),"") In B4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,2,0)) In C4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,3,0)) Format Columns A and B for date, and Column C for percentage. Drag the formulas down as far as needed. Best regards, B. R. Ramachandran "JeffK" wrote: I have a 3 Column Table: Begin Date, End Date, Interest. On a separate worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
table lookup
That's it! Thanks a bunch.
It was the range for the table that caused the problems. Awesome "B. R.Ramachandran" wrote: Hi Jeff, I tested the formulas again, and they are working fine for me (I am using Excel 2007, but I think that it shouldn't matter). I even extended to Table to contain 10 more rows of Begin and End dates and percentages, and also varied the Begin and End dates in the Input page, to make sure that the formulas work; they do work fine. Also, the "ROW(A1)" parts in the formula don't have to refer to Sheet1 (ROW(A1) just generates the number 1, and is not sheet-specific). Please try again. I am cutting/pasting the formulas again from my Excel trial (This time the formulas contain the range A2:D18 instead of A2:D8 posted earlier to accommodate an extended Table. Change the range aqppropriately to suit to your worksheet. =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$18,4)=VLOOKUP($B$ 1,Sheet1!$A$2:$D$18,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $18,4)+ROW(A1)-1),"") =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,2,0)) =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,3,0)) Hope it works this time! Thanks, B. R. Ramachandran "JeffK" wrote: I input the formula and adjusted the table/sheets to mirror your formula (to get it working first and make adjustments later) Only the first 4 rows are copied then nothing. I'm guessing by looking at the first formula, the portion that read " +ROW(A1) " doesn't seem to reference to sheet1 and I think that's the reason I get only 4 rows of date (formula entered in A4 on the input sheet) Not sure how to fix. Tried but I get errors. "B. R.Ramachandran" wrote: Hi Jeff, There may be a more elegant approach, but the following seems to work. In the Table page, create a helper column D that displays the row numbers (So, D2=ROW(D2), and fill down the column). In the Input page (I assume that the Begin and End dates are in B1 and B2 respectively). Enter the following formulas in A4, B4, and C4 respectively (The formulas assume that the Table page's name is Sheet1. If not, enter the sheet's actual name in place of "Sheet1" in every occurrence in the formulas. In A4, =IF(VLOOKUP($B$2,Sheet1!$A$2:$D$8,4)=VLOOKUP($B$1 ,Sheet1!$A$2:$D$8,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D $8,4)+ROW(A1)-1),"") In B4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,2,0)) In C4, =IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,3,0)) Format Columns A and B for date, and Column C for percentage. Drag the formulas down as far as needed. Best regards, B. R. Ramachandran "JeffK" wrote: I have a 3 Column Table: Begin Date, End Date, Interest. On a separate worksheet, if a user enters the a specified Begin and End dates, below the entry,I want to list that portion of the table that falls between the dates. (assume the table is A2-C8) Begin Date End Date Interest 1-Jan 5-Feb 2.00% 6-Feb 15-Mar 2.25% 16-Mar 4-Apr 2.50%* 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75%* 8-Jul 15-Aug 3.00% Input page Begin Date = Mar 19 End = June 6 16-Mar 4-Apr 2.50% 5-Apr 21-Apr 2.25% 22-Apr 1-Jun 2.50% 2-Jun 7-Jul 2.75% I appreciate your help on this one |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup more than one value across a table | Excel Worksheet Functions | |||
| Table lookup | Excel Worksheet Functions | |||
| Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| lookup a value on a table | Excel Worksheet Functions | |||
Linear Mode
