Hey all,
I created a range "dates" with an x-amount of rows.
I would like to offer 2 drop down lists (via data validation):
Start date
=dates
End date.
The end date should be >= start date AND <= latest date in the range "dates".
=AND(B5>=B4; B5<=MAX(dates))
This formula works, but only when you select CUSTOM in the data validation.
It doesn't work when you chose LIST.
Can any1 help me?
Last edited by dunnobe; 11-14-2017 at 06:27 AM.
I'd use two named ranges, like this. DV/formulae in shaded cells. Happy to explain if this meets you needs.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hey Glenn,
Thank you for your quickly reply.
Isn't there a solution without having to make an extra column/range? I don't have much "room" for this in my current assignment.
Can the IFERROR function be integrated in the list formula?
Google doesn't help much although this should be a pretty common question.
Alternate method.
Dynamic named range formula for end date.
Assuming dates in Column A and start date in G2.
=OFFSET(Sheet1!$A$2,MATCH(Sheet1!G2,dates,0),,ROWS(dates)-MATCH(Sheet1!G2,dates,0))
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Very neat CK76. One tweak. Use a dynamic formula for the start dates:
=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,COUNTA(Sheet1!$A$2:$A$1000))
Hey CK76,
Where exacly do you put the OFFSET formula. I can't find this in your sheet, nor the end_date range that you used in the data validation list.
Did you only create this in the Name Manager?
(I've never used OFFSET before).
Thank you for your help,
Bram
Go to formula ribbon tool and find name manager. Named range is there. Formula is used in the named range.
As Glenn suggested, use dynamic named range for "dates" as well.
0.JPG
I guess that your solution is similar to this:
https://www.excel-university.com/dat...ion-drop-down/
I'll see if I can get it to work. Getting a headache now :p
Big it up for both of you!
Hi guys,
A small question for a beter understanding.
How do I make sure that the end date can be the same as the start date?
The end date is always atleast 1 day after the start date.
Last edited by dunnobe; 11-13-2017 at 11:04 AM.
Hmm? In your original post you had >= start date.
So it should be >start date?
In that case just add 1 to start date.
I.E. MATCH(Sheet1!G2+1,dates,0)
Hey CK76,
Mmm, I can't get this +1 thing to work.
When I do +1, the end date is atleast +2 days ahead of the start date.
When I do -1, the drop down list freezes.
Could you or Glenn Kenney have a look?
(I added test.xlsx to the attachments. The info is in the worksheet called 'Summary'.)
(Sorry, still learning a lot.)
Best regards,
Bram
Correction: I get freeze problem too!
Try
for "End_date"
=OFFSET(Table1[Date lg.],MATCH(Summary!$B$4,dates,0)-1,,ROWS(dates)-MATCH(Summary!$B$4,dates,0)+1)
Like this, maybe??
I see JT has jumped in d got there while I was eating my breakfast!! Same solution.
Thanks guys!
I knew I had to work with the 1 or +1 so I definitely learned something.
+1 rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks