I have a workbook that has a large amount of data. I have attached a sample of the data. I want to set a formula that searches columns K to S for dates that are within 30 days of the current date (constantly changing). If the Planned date matches the criteria, I would like to know the "Doc. No" from column E. Any help would be appreciated. I'm not even sure where to start.
When you say "within 30 days of the current date", do you mean you are looking ahead (i.e. between today's date and 30 days hence), or looking backwards (from 30 days ago up to today), or both (from 30 days ago up to 30 days hence)?
Do you want to see the Document numbers which match the criteria on a different sheet?
Pete
Yes, looking ahead. I was thinking I would have a cell that I can enter any date at any time, and then the formula(s) would return the values that match the criteria to that date.
The document numbers can be on a different sheet. I'm not too picky about that. Thanks!
Yes, looking ahead. I was thinking I would have a cell that I can enter any date at any time, and then the formula(s) would return the values that match the criteria to that date.
The document numbers can be on a different sheet. I'm not too picky about that. Thanks!
One issue here is that the dates are entered as text. This can be corrected by selecting the cells one at a time and from the caution that will appear to the left select 'Change xx to 20xx' from the drop down, I have changed the first few for the purpose of demonstrating my solution which incorporates the formula:This is not a finished product, but if it is headed in the right direction, I imagine that the idea can be expanded.
Please Login or Register to view this content.
Copy of Copy of Engineering Deliverables Status Report PRELIMNIARY.xlsx
Let me know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
So once all the cells with the dates are changed to a four digit year, how would the formula be expanded to look at the whole sheet?
thanks for your help.
I see that where you have data in multiple columns (below row 73) then all the planned dates are the same. Will this always be the case? If so, we only need to look at the first column to check if they meet the criteria.
Pete
Unfortunately, no. The actual file has about 6000 rows and most of the time the data in multiple columns do not have the same planned dates.
The following array formula (Ctrl+Shift+Enter) will give the results that you are looking for as seen in the attached file. By the way I have made changes to EDSR.207204-08876.13-NOV-15!K5:M5 to illustrate how this would work.Attachment 433293Please Login or Register to view this content.
As pursuant to questions about the attachment and formula: Columns D and H were columns where I was working on parts of the final equation that wound up in column B. I have removed them and re-attached the file. The formula's reference to column J is just to keep up with the row numbers for the "Planned" dates. Once the issues with the text entered dates has been rectified, it should be easier to see how this works.
Let me know if you have any further questions.
Last edited by JeteMc; 12-02-2015 at 06:58 PM. Reason: Answering questions about the file and formula
The columns identified as dates should have dates and only dates and not the variety of values that currently exist. Having data properly entered will save you a lot of time and effort to produce reports like you have just asked for. If there is a description that belongs to a date, enter it in a separate column. In addition the data should be 1 row per record and one column per field.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Okay. My follow questions are:
How do I modify this formula so that I can enter any date at the top and the formula returns the results from the entire worksheet (searching for dates located in columns K-S and rows 4-200)
thanks!
The formula is already checking the rows down to row 202, you can change that by changing the ROW(J$3:J$202) parts of the formula that read so that the part I have highlighted in red is whatever number of rows you want to formula to check. You can clear the data validation from Sheet1!A1 so that you can type in any date you want. Let me know if you have any other questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks