Hi All,
I would like to create a dropdown list in the 'Substitute Vehicle' column. The list must show all 'Substitute vehicles' except those whose 'Date out' has been recorded but the Date in is still blank.
Thanks.
Hi All,
I would like to create a dropdown list in the 'Substitute Vehicle' column. The list must show all 'Substitute vehicles' except those whose 'Date out' has been recorded but the Date in is still blank.
Thanks.
see attached workbook
- sheet3 contains table of values
- source for DataValidation in (sheet1)columnB is amended to NamedRange "Available"
- NamedRange "Available", RefersTo formula is
Formula:Please Login or Register to view this content.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
That's not what I want.. Why would I enter DateOut and DateIN in both Sheets Sheet1 and Sheet3..??
Last edited by alipezu; 02-13-2018 at 12:04 AM.
Your workbook does not contain much data - and no vehicle is currently listed more than once.
Will the list on sheet 1 include the same substitute vehicle more than once?
Are you using Excel 2007?
Thanks
This is just a sample workbook. In my original workbook there are thousands of records where the vehicles and substitute vehicles are existing many times.
I am using Excel 2010.
As shown in the figure below the substitute vehicle CCC985 and CCC120 have Dates Out only and must not be displayed in the drop down..
Untitled.png
Last edited by alipezu; 02-13-2018 at 02:19 AM.
see attached workbook
The list of available vehicles for the dropdown is in ColumnC on Sheet2
- column C is blank if vehicle is not available
The formula is an array formula, which must be committed with {CTRL}{SHIFT}{ENTER}
In C2 copied down:
Formula:Please Login or Register to view this content.
It will not work if you try to type in the curly brackets { }
but the formula should look like this:
Formula:Please Login or Register to view this content.
The data validation (Sheet1) column B has Source: =Available
Available is a dynamic NamedRange with RefersTo:
Formula:Please Login or Register to view this content.
Last edited by kev_; 02-13-2018 at 04:57 AM.
Its perfect now!! Thank you so much for the great help!!
You are welcome. Thanks for reps and for marking thread SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks