Data validation dependant drop down on unsorted list

[Reacher21]

I can't see a solution on contextures.com or here so hoping someone can put
me out of my misery!
I have a master list of vessel names on Sheet1 Column A. On Sheet2 is my
data repository where I record all voyages of vessels I'm tracking and some
vessels make multiple voyages differentiated by a different departure date.
The vessel name for each voyage is chosen by validated drop down based on
list in sheet1. Next to that is the departure date and other details related
to cargo etc.
On Sheet3 I wish to use a template invoice which has a drop down for vessel
name, and then a drop down to choose from the departure dates related to the
chosen vessel. The data on Sheet2 is sorted by departure date and I would
like to keep it that way for other reasons. I set up contextures' solution
for sorted list only to realise that it must be sorted by the vessel name. I
don't want to use pivots or macros if I can help it. Thanks for any help.

 

[T. Valko]

Considering all the things you don't want to do, then this becomes "work
intensive".

What you'd have to do is make a separate list for each unique vessel with
its departure dates then use that data as the source for your drop down.

 

[Reacher21]

Hi Biff, Thanks for your response. I guess if there is a way of using pivots
and/or VBA that doesn't require the end-user of this file to be literate in
using either pivots or VBA then I'm all ears. I need it to be automated which
is why I was looking at the formula route. Any suggestions?

 

[T. Valko]

Here's a small sample file that uses formulas to do this.

xReacher21.xls 20 kb

http://cjoint.com/?mdhLqagJJc

If you think you can use this and you have any questions just ask.

 

[Reacher21]

Thank you very much Biff. The list of dates per vessel was preferable to
running VB and I built in allowance for new vessels etc. Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Roger D]

I am looking to check out xReacher21.xls to see if it will do what I am looking for. Unfortunately, the link is dead. Any chance of posting a new link for the file?



T. Valko wrote:

Here is a small sample file that uses formulas to do this.xReacher21.
03-Dec-09

Here is a small sample file that uses formulas to do this.

xReacher21.xls 20 kb

http://cjoint.com/?mdhLqagJJc

If you think you can use this and you have any questions just ask.

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

Data validation dependant drop down on unsorted list
I cannot see a solution on contextures.com or here so hoping someone can put
me out of my misery!
I have a master list of vessel names on Sheet1 Column A. On Sheet2 is my
data repository where I record all voyages of vessels I am tracking and some
vessels make multiple voyages differentiated by a different departure date.
The vessel name for each voyage is chosen by validated drop down based on
list in sheet1. Next to that is the departure date and other details related
to cargo etc.
On Sheet3 I wish to use a template invoice which has a drop down for vessel
name, and then a drop down to choose from the departure dates related to the
chosen vessel. The data on Sheet2 is sorted by departure date and I would
like to keep it that way for other reasons. I set up contextures' solution
for sorted list only to realise that it must be sorted by the vessel name. I
do not want to use pivots or macros if I can help it. Thanks for any help.

Considering all the things you do not want to do, then this becomes
Considering all the things you do not want to do, then this becomes "work
intensive".

What you would have to do is make a separate list for each unique vessel with
its departure dates then use that data as the source for your drop down.

--
Biff
Microsoft Excel MVP

Hi Biff, Thanks for your response.
Hi Biff, Thanks for your response. I guess if there is a way of using pivots
and/or VBA that does not require the end-user of this file to be literate in
using either pivots or VBA then I am all ears. I need it to be automated which
is why I was looking at the formula route. Any suggestions?

:

Here is a small sample file that uses formulas to do this.xReacher21.
Here is a small sample file that uses formulas to do this.

xReacher21.xls 20 kb

http://cjoint.com/?mdhLqagJJc

If you think you can use this and you have any questions just ask.

--
Biff
Microsoft Excel MVP

Thank you very much Biff.
Thank you very much Biff. The list of dates per vessel was preferable to
running VB and I built in allowance for new vessels etc. Thanks again.

:

You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
A Framework to Animate WPF and Silverlight Pages Similar to the PowerPoint Slides
http://www.eggheadcafe.com/tutorial...0-c7354940d878/a-framework-to-animate-wp.aspx