|
Lookup Data
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest On a seperate tab of the spreadsheet, I'd like to have my data automatically populated from the first tab based on the Purch Date. For example, if I have 5 lines of data on tab one...and three of the lines have the same purch date, I'd like excel to automatically list this data on tab two once I change a date on tab two. I've tried using Index and Lookup formulas, but they will only list the first line item that matches this Purch Date...not the subsequent data. How can I have these other lines of data extracted to the 2nd tab without duplicating lines...and without omitting lines? |
Lookup Data
One way using non-array formulas to achieve it dynamically ..
Source data is assumed in sheet: X, from row2 down in cols A to E, with col A = key col (Purchase Date) In another sheet: Y (say), Paste the same col headers into A1:E1 Let's say G1 will house the input of the purchase date (of interest) Put in A2: =IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0))) Copy A2 to E2 Put in F2: =IF(X!A2="","",IF(X!A2=$G$1,ROW(),"")) (Leave F1 empty) Then just select A2:F2 and copy down to cover the max expected extent of data in X. Format cols A & B as dates. Hide away the criteria col F if desired. Cols A to F will return the required results dynamically from X, ie only the lines with purchase dates equal to that input in G1. All results will be neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: On one tab of my spreadsheet, I have the following data in Columns Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest On a seperate tab of the spreadsheet, I'd like to have my data automatically populated from the first tab based on the Purch Date. For example, if I have 5 lines of data on tab one...and three of the lines have the same purch date, I'd like excel to automatically list this data on tab two once I change a date on tab two. I've tried using Index and Lookup formulas, but they will only list the first line item that matches this Purch Date...not the subsequent data. How can I have these other lines of data extracted to the 2nd tab without duplicating lines...and without omitting lines? |
Lookup Data
Max....
I think I figured out what the problem was.... Will this formula only work if the column headers are in row 1? When I delete all the other header information....everything works. Can I not have the same thing work if my column headers start on row 7 for both sheets? Thanks again "Brian" wrote: Max, Thanks very much for your assistance....I think this is exactly what I need...but for some reason I still can't get it to work correctly. I did a test using the example you listed below....but when I try the same thing with the actual spreadsheet, my data field come up blank. The only revisions I made to your example below is the following: My Column Headers run from column A7 to Column J7. I then have column K represent column F in your example And instead of putting my date in G1...I have it in C3 instead. My formulas in column a thru J a =IF(ROW(A7)COUNT($K:$K),"",INDEX(Investments!A:A, MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in. My formula in column K is =IF(Investments!A7="","",IF(Investments!A7=$C$3,RO W(),"")) Any idea why this still isn't working? Thanks again...I really appreciate it!!! "Max" wrote: One way using non-array formulas to achieve it dynamically .. Source data is assumed in sheet: X, from row2 down in cols A to E, with col A = key col (Purchase Date) In another sheet: Y (say), Paste the same col headers into A1:E1 Let's say G1 will house the input of the purchase date (of interest) Put in A2: =IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0))) Copy A2 to E2 Put in F2: =IF(X!A2="","",IF(X!A2=$G$1,ROW(),"")) (Leave F1 empty) Then just select A2:F2 and copy down to cover the max expected extent of data in X. Format cols A & B as dates. Hide away the criteria col F if desired. Cols A to F will return the required results dynamically from X, ie only the lines with purchase dates equal to that input in G1. All results will be neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: On one tab of my spreadsheet, I have the following data in Columns Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest On a seperate tab of the spreadsheet, I'd like to have my data automatically populated from the first tab based on the Purch Date. For example, if I have 5 lines of data on tab one...and three of the lines have the same purch date, I'd like excel to automatically list this data on tab two once I change a date on tab two. I've tried using Index and Lookup formulas, but they will only list the first line item that matches this Purch Date...not the subsequent data. How can I have these other lines of data extracted to the 2nd tab without duplicating lines...and without omitting lines? |
Lookup Data
Max,
Thanks very much for your assistance....I think this is exactly what I need...but for some reason I still can't get it to work correctly. I did a test using the example you listed below....but when I try the same thing with the actual spreadsheet, my data field come up blank. The only revisions I made to your example below is the following: My Column Headers run from column A7 to Column J7. I then have column K represent column F in your example And instead of putting my date in G1...I have it in C3 instead. My formulas in column a thru J a =IF(ROW(A7)COUNT($K:$K),"",INDEX(Investments!A:A, MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in. My formula in column K is =IF(Investments!A7="","",IF(Investments!A7=$C$3,RO W(),"")) Any idea why this still isn't working? Thanks again...I really appreciate it!!! "Max" wrote: One way using non-array formulas to achieve it dynamically .. Source data is assumed in sheet: X, from row2 down in cols A to E, with col A = key col (Purchase Date) In another sheet: Y (say), Paste the same col headers into A1:E1 Let's say G1 will house the input of the purchase date (of interest) Put in A2: =IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0))) Copy A2 to E2 Put in F2: =IF(X!A2="","",IF(X!A2=$G$1,ROW(),"")) (Leave F1 empty) Then just select A2:F2 and copy down to cover the max expected extent of data in X. Format cols A & B as dates. Hide away the criteria col F if desired. Cols A to F will return the required results dynamically from X, ie only the lines with purchase dates equal to that input in G1. All results will be neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: On one tab of my spreadsheet, I have the following data in Columns Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest On a seperate tab of the spreadsheet, I'd like to have my data automatically populated from the first tab based on the Purch Date. For example, if I have 5 lines of data on tab one...and three of the lines have the same purch date, I'd like excel to automatically list this data on tab two once I change a date on tab two. I've tried using Index and Lookup formulas, but they will only list the first line item that matches this Purch Date...not the subsequent data. How can I have these other lines of data extracted to the 2nd tab without duplicating lines...and without omitting lines? |
Lookup Data
Can I not have the same thing work
if my column headers start on row 7 for both sheets? Presume you mean if source data starts in row7 (instead of in row2). Yes, of course it can still work but we'd need to make an arithmetic adjustment in the extract formula. Here's a working sample configured to suit for easy reference: http://cjoint.com/?iqiAC6FnX8 Filtering from another sht.xls Source sheet is named: Investments, data in cols A to J, headers in A6:J6, data from row7 down In a new sheet, Y (say) [Purch] Date of interest will be input in L1 (or the date could be selected from a DV in L1, say) In A2: =IF(ROW(A1)COUNT($K:$K),"", INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5)) Copy A2 to J2 [The "+5" is an arithmetic adjustment as source data starts in row7, and we are pulling it from row2 here in Y. ROW(A1) is always used in the starting formula cell, irrespective of where this start cell may be. ROW(A1) is used as an incrementer in the formula.] In K2: =IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),"")) Select A2:K2, fill down to cover the max expected extent of data in Investments. Format cols A & B as dates -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: Max.... I think I figured out what the problem was.... Will this formula only work if the column headers are in row 1? When I delete all the other header information....everything works. Can I not have the same thing work if my column headers start on row 7 for both sheets? |
Lookup Data
Here's a more enduring <g link to the working sample:
http://savefile.com/files/5594819 Filtering from another sht.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Lookup Data
Max,
Thanks very much for your assistance.... For some reason I still can't get this to work correctly. I've done everything you suggested...and it works perfectly with a test file....but when I try to apply the formulas to my actual spreadsheet, the data keeps coming up blank. I can't seem to figure out what I'm doing wrong. I'll continue to play around with it...because I know it should work and your suggestion is exactly what I need. Thanks again "Max" wrote: Here's a more enduring <g link to the working sample: http://savefile.com/files/5594819 Filtering from another sht.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Lookup Data
Perhaps the source purchase "dates" in Investments' col A aren't real dates
recognized by Excel. Try this on a spare copy of your actual data .. Select only the "dates" range in Investments' col A Click Data Text To Columns Click Next Next to proceed to step 3 of the wizard In step 3, under "Col data format", check "Date", then select the appropriate date format from the droplist, eg: MDY Click Finish The above should convert all the dates to real dates recognized by Excel, and everything should work fine now. Alternatively, we could also give this a try .. Put instead in K2, and fill down: =IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),"")) The "+0" may suffice to coerce the text dates in Investments' col A to real dates Let me know how it goes .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote in message ... Max, Thanks very much for your assistance.... For some reason I still can't get this to work correctly. I've done everything you suggested...and it works perfectly with a test file....but when I try to apply the formulas to my actual spreadsheet, the data keeps coming up blank. I can't seem to figure out what I'm doing wrong. I'll continue to play around with it...because I know it should work and your suggestion is exactly what I need. Thanks again "Max" wrote: Here's a more enduring <g link to the working sample: http://savefile.com/files/5594819 Filtering from another sht.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote: Can I not have the same thing work if my column headers start on row 7 for both sheets? Presume you mean if source data starts in row7 (instead of in row2). Yes, of course it can still work but we'd need to make an arithmetic adjustment in the extract formula. Here's a working sample configured to suit for easy reference: http://cjoint.com/?iqiAC6FnX8 Filtering from another sht.xls Source sheet is named: Investments, data in cols A to J, headers in A6:J6, data from row7 down In a new sheet, Y (say) [Purch] Date of interest will be input in L1 (or the date could be selected from a DV in L1, say) In A2: =IF(ROW(A1)COUNT($K:$K),"", INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5)) Copy A2 to J2 [The "+5" is an arithmetic adjustment as source data starts in row7, and we are pulling it from row2 here in Y. ROW(A1) is always used in the starting formula cell, irrespective of where this start cell may be. ROW(A1) is used as an incrementer in the formula.] In K2: =IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),"")) Select A2:K2, fill down to cover the max expected extent of data in Investments. Format cols A & B as dates -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: Max.... I think I figured out what the problem was.... Will this formula only work if the column headers are in row 1? When I delete all the other header information....everything works. Can I not have the same thing work if my column headers start on row 7 for both sheets? |
Lookup Data
That did it Max!
I used your suggestion and now everything has worked perfectly!!! Thanks again for your help!!!! I never would've been able to do it without you! "Max" wrote: Perhaps the source purchase "dates" in Investments' col A aren't real dates recognized by Excel. Try this on a spare copy of your actual data .. Select only the "dates" range in Investments' col A Click Data Text To Columns Click Next Next to proceed to step 3 of the wizard In step 3, under "Col data format", check "Date", then select the appropriate date format from the droplist, eg: MDY Click Finish The above should convert all the dates to real dates recognized by Excel, and everything should work fine now. Alternatively, we could also give this a try .. Put instead in K2, and fill down: =IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),"")) The "+0" may suffice to coerce the text dates in Investments' col A to real dates Let me know how it goes .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote in message ... Max, Thanks very much for your assistance.... For some reason I still can't get this to work correctly. I've done everything you suggested...and it works perfectly with a test file....but when I try to apply the formulas to my actual spreadsheet, the data keeps coming up blank. I can't seem to figure out what I'm doing wrong. I'll continue to play around with it...because I know it should work and your suggestion is exactly what I need. Thanks again "Max" wrote: Here's a more enduring <g link to the working sample: http://savefile.com/files/5594819 Filtering from another sht.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote: Can I not have the same thing work if my column headers start on row 7 for both sheets? Presume you mean if source data starts in row7 (instead of in row2). Yes, of course it can still work but we'd need to make an arithmetic adjustment in the extract formula. Here's a working sample configured to suit for easy reference: http://cjoint.com/?iqiAC6FnX8 Filtering from another sht.xls Source sheet is named: Investments, data in cols A to J, headers in A6:J6, data from row7 down In a new sheet, Y (say) [Purch] Date of interest will be input in L1 (or the date could be selected from a DV in L1, say) In A2: =IF(ROW(A1)COUNT($K:$K),"", INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5)) Copy A2 to J2 [The "+5" is an arithmetic adjustment as source data starts in row7, and we are pulling it from row2 here in Y. ROW(A1) is always used in the starting formula cell, irrespective of where this start cell may be. ROW(A1) is used as an incrementer in the formula.] In K2: =IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),"")) Select A2:K2, fill down to cover the max expected extent of data in Investments. Format cols A & B as dates -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: Max.... I think I figured out what the problem was.... Will this formula only work if the column headers are in row 1? When I delete all the other header information....everything works. Can I not have the same thing work if my column headers start on row 7 for both sheets? |
Lookup Data
Delighted to hear that, Brian !
Thanks for calling home .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: That did it Max! I used your suggestion and now everything has worked perfectly!!! Thanks again for your help!!!! I never would've been able to do it without you! |
Lookup Data
Max,
I have been reading this thread with interest as it goes 90% of the way to solving a problem I have. Is there a way of amending your formula to return data between two dates, i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29 Jan 07 based on purchdate? I very much look forward to hearing from you. Kind regards, Paul "Max" wrote: Delighted to hear that, Brian ! Thanks for calling home .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Brian" wrote: That did it Max! I used your suggestion and now everything has worked perfectly!!! Thanks again for your help!!!! I never would've been able to do it without you! |
Lookup Data
.. to return data between two dates ..
Sure, we just need to amend the earlier criteria formula in col K Instead of: In K2, and fill down: =IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),"")) Assuming the (purchase) startdate will be input in L1, with enddate input in L2 Use this amended criteria in K2, and fill down: =IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),"")) No change to the rest of the set-up. It should work just as well as before <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lancer940" wrote: Max, I have been reading this thread with interest as it goes 90% of the way to solving a problem I have. Is there a way of amending your formula to return data between two dates, i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29 Jan 07 based on purchdate? I very much look forward to hearing from you. Kind regards, Paul |
Lookup Data
Hi Max,
Thanks very much for the prompt reply. Unfortunately I am having some problems. My formula looks like this: =IF(ROW(B3)COUNT($S:$S),"",INDEX(data!A:A,MATCH(S MALL($S:$S,ROW(B3)),$S:$S,0))) My problem is that the array "data" does not seem to be picked up in the formula -Excel starts looking for it in another workbook. When I add the "!" in the formula bar the array just becomes text. Also, I assume that "data" should be an array on the source data sheet. Is that right? Paul "Max" wrote: .. to return data between two dates .. Sure, we just need to amend the earlier criteria formula in col K Instead of: In K2, and fill down: =IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),"")) Assuming the (purchase) startdate will be input in L1, with enddate input in L2 Use this amended criteria in K2, and fill down: =IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),"")) No change to the rest of the set-up. It should work just as well as before <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lancer940" wrote: Max, I have been reading this thread with interest as it goes 90% of the way to solving a problem I have. Is there a way of amending your formula to return data between two dates, i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29 Jan 07 based on purchdate? I very much look forward to hearing from you. Kind regards, Paul |
Lookup Data
Max,
Final update. Got everything to work just fine. Thanks to your previous post. The formula in cell A3 of the output worksheet is ('DT Stats' is the name of the source data worksheet): =IF(ROW(A1)COUNT($S:$S),"",INDEX('DT Stats'!A:A,MATCH(SMALL($S:$S,ROW(A1)),$S:$S,0))) In cell S3: =IF(OR('DT Stats'!B3="",$B$1="",$D$1=""),"",IF(AND('DT Stats'!B3+0=$B$1,'DT Stats'!B3+0<=$D$1),ROW(),"")) The data in both worksheets starts from Row 3. The date from the source data ('DT Stats') is in Col B. The from/to dates are in cells B1 and D1. Thank you very much for taking the time to help me out. Kind regards, Paul In "Lancer940" wrote: Hi Max, Thanks very much for the prompt reply. Unfortunately I am having some problems. My formula looks like this: =IF(ROW(B3)COUNT($S:$S),"",INDEX(data!A:A,MATCH(S MALL($S:$S,ROW(B3)),$S:$S,0))) My problem is that the array "data" does not seem to be picked up in the formula -Excel starts looking for it in another workbook. When I add the "!" in the formula bar the array just becomes text. Also, I assume that "data" should be an array on the source data sheet. Is that right? Paul "Max" wrote: .. to return data between two dates .. Sure, we just need to amend the earlier criteria formula in col K Instead of: In K2, and fill down: =IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),"")) Assuming the (purchase) startdate will be input in L1, with enddate input in L2 Use this amended criteria in K2, and fill down: =IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),"")) No change to the rest of the set-up. It should work just as well as before <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lancer940" wrote: Max, I have been reading this thread with interest as it goes 90% of the way to solving a problem I have. Is there a way of amending your formula to return data between two dates, i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29 Jan 07 based on purchdate? I very much look forward to hearing from you. Kind regards, Paul |
Lookup Data
.. Got everything to work just fine.
Glad to hear that, Paul ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Jan 25, 1:55 am, Lancer940 wrote: Max, Final update. Got everything to work just fine. Thanks to your previous post. The formula in cell A3 of the output worksheet is ('DT Stats' is the name of the source data worksheet): =IF(ROW(A1)COUNT($S:$S),"",INDEX('DT Stats'!A:A,MATCH(SMALL($S:$S,ROW(A1)),$S:$S,0))) In cell S3: =IF(OR('DT Stats'!B3="",$B$1="",$D$1=""),"",IF(AND('DT Stats'!B3+0=$B$1,'DT Stats'!B3+0<=$D$1),ROW(),"")) The data in both worksheets starts from Row 3. The date from the source data ('DT Stats') is in Col B. The from/to dates are in cells B1 and D1. Thank you very much for taking the time to help me out. Kind regards, Paul |
| All times are GMT +1. The time now is 02:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com