I have attached a dummy file DummyWorkbook.xlsx with a worksheet for data and a worksheet for desired results. I want to be able to enter a date and description on one sheet and then on my second sheet, enter a date range and have the rows below populate with any dates and their corresponding descriptions from the 1st sheet. I do not want there to be any empty rows and would ideally enter a formula in a single cell (not using vba) to achieve this.
Last edited by morgens; 02-13-2012 at 08:47 PM. Reason: Solved
Anyone? Even if it can't be done without VBA, I would appreciate ANY info you can give me. Thank you!
B1: =MIN(Data!A:A)
D1: =MAX(Data!A:A)
B5: =IF($B$1-1+ROW(A1)>$D$1,"",VLOOKUP($B$1-1+ROW(A1),Data!$A:$B,2,0))
...copy B5 down as far as you'd like.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
thanks for the response. I appreciate you taking the time. It's great that the list begins with the start date, regardless of what start date is on the data sheet. Unfortunately this doesn't quite solve my problem. I still have two issues here:
1. If there are multiple descriptions for the same date, it will only return the first occurrence.
2. Also, it is leaving a slot for each date, so I added a description for 1/12 and it listed it in the 12th spot, but left all cells between that description and the previous date as #N/A. Ideally, there would be no blank or n/a cells.
any idea on how to solve one or both?
thanks again.
Hi
In data sheet in Column A i have enetered the formula to increase by one if the date fallss between the range date given in desired results sheet.
Then in "Desired Result"s sheet i use vlookup to extrect the rows useing row function.=IF(AND(B2>='Desired Results'!$B$1,B2<='Desired Results'!$D$1),A1+1,A1)
See the attached=IF(ISERROR(VLOOKUP(ROW(A1),Data!$A$2:$C$7,3,FALSE)),"",VLOOKUP(ROW(A1),Data!$A$2:$C$7,3,FALSE))
Regards
Last edited by mahju; 02-12-2012 at 11:32 AM.
Mahju
Mark the thread as solved if you are satisfied with the answer
Rule 9
Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
You're saying your sample sheet originally doesn't actually show your entire scenario? Redo that and be sure to fully demonstrate what you're doing, not just parts of it. Thanks.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
mahju,
thank you for your response. I am afraid those formulas do not achieve the desired results. The 1st Description of the desired results does return correctly, but not necessarily the rest or the complete desired date range. Thank you very much for the effort.
JBeaucaire,
I apologize for not including enough data to start. I have added a few rows to the data set as well as a second possibility for desired results. Hopefully this will give a more complete picture of what I am looking for.
You can see that there will be multiple descriptions for a given date.
The Start and End dates will be manually entered so that I can choose a date range to summarize.
I will need to print out this summary on a weekly basis. The data set will likely have over 1,000 entries by years end, but I would still like to be able to pull descriptions within a given date range and print it out without spaces between the descriptions.
I guess I am basically looking to replace the manual action of sorting by date on the data sheet, copying the descriptions within the desired range and pasting on a summary sheet. There are other calculations for the given date range that are included on that summary as well that I have already figured out.
thanks again for your help and please let me know if you need any additional information.
Attachment 141086DummyWorkbook_updated.xlsx
Try this
In B5, with CTRL+SHIFT+ENTER, rather than just ENTER
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Data!B:B,SMALL(IF(Data!A$2:A$40>=B$1,IF(Data!A$2:A$40<=D $1,ROW(Data!A$2:A$40))),ROWS(B$5:B5)))))
Then copy down.
Assume you are on XL 2003
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
HiOriginally Posted by morgens
I checked if you set the formulae as in sheet it will give all description
any way thanks fo your coments
Regards
Mahju
Mark the thread as solved if you are satisfied with the answer
Rule 9
Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
Haseeb A,
This appears to work! thank you. Now I just need to take some time to understand why. Thank you so very much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks