1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Data validation list that is conditional on A to return B

    From Sam Hopper@21:1/5 to Sam Hopper on Tue May 12 12:46:48 2020
    On Tuesday, May 12, 2020 at 3:45:58 PM UTC-4, Sam Hopper wrote:
    I have a table that shows project numbers and their status ranging from 100 to 400. I want to have a data validation list that shows all project numbers (Column A) where the project status (Column D) is between 200 - 299.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From sebpgood@gmail.com@21:1/5 to All on Tue May 12 12:45:53 2020
    I have a table that shows project numbers and their status ranging from 100 to 400. I want to have a data validation list that shows all project numbers (Column A) where the project status (Column D) is between 200 - 299.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Tue May 12 17:11:56 2020
    I have a table that shows project numbers and their status ranging from 100 to 400. I want to have a data validation list that shows all project numbers (Column A) where the project status (Column D) is between 200 - 299.

    See here...

    https://www.contextures.com/exceldependentdropdownsetup.html

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Sam Hopper@21:1/5 to All on Wed May 13 12:07:00 2020
    My table is set up as below. I want the data valaidation to only show projects where the status is between 200 to 299. The video does not show how to do this easily.

    Project Name Status
    42900 Car 100
    42901 Bike 210
    42902 truck 250
    42903 Van 300

    I need the drop down to only show me project numbers 42901 and 42902 as they are the only ones where status is between 200 and 299. The list I actually have is a table about 100 rows long.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Wed May 13 23:45:10 2020
    My table is set up as below. I want the data valaidation to only show projects where the status is between 200 to 299. The video does not show how to do this easily.

    Project Name Status
    42900 Car 100
    42901 Bike 210
    42902 truck 250
    42903 Van 300

    I need the drop down to only show me project numbers 42901 and 42902 as they are the only ones where status is between 200 and 299. The list I actually have is a table about 100 rows long.

    There are several videos there; - did you watch all of the Dependant DV ones?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Zaidy036@21:1/5 to All on Thu May 14 16:49:57 2020
    On 5/13/2020 11:45 PM, GS wrote:
    My table is set up as below.  I want the data valaidation to only show
    projects where the status is between 200 to 299.  The video does not
    show how to do this easily.

    Project Name  Status
    42900   Car   100
    42901   Bike  210
    42902   truck 250
    42903   Van   300

    I need the drop down to only show me project numbers 42901 and 42902
    as they are the only ones where status is between 200 and 299.  The
    list I actually have is a table about 100 rows long.

    There are several videos there; - did you watch all of the Dependant DV
    ones?

    Conditional format the row or a cell to color fill based on value in
    Status. Cold have range of colors indicating range of values.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Thu May 14 22:49:44 2020
    On 5/13/2020 11:45 PM, GS wrote:
    My table is set up as below.  I want the data valaidation to only show
    projects where the status is between 200 to 299.  The video does not show >>> how to do this easily.

    Project Name  Status
    42900   Car   100
    42901   Bike  210
    42902   truck 250
    42903   Van   300

    I need the drop down to only show me project numbers 42901 and 42902 as
    they are the only ones where status is between 200 and 299.  The list I
    actually have is a table about 100 rows long.

    There are several videos there; - did you watch all of the Dependant DV
    ones?

    Conditional format the row or a cell to color fill based on value in Status. Cold have range of colors indicating range of values.

    Hmm! Good idea to 'flag' w/ fill color[s], but I'm now thinking Advanced Filter on Status col.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Sam Hopper@21:1/5 to All on Fri May 15 07:32:20 2020
    On Wednesday, May 13, 2020 at 11:45:17 PM UTC-4, GS wrote:
    My table is set up as below. I want the data valaidation to only show projects where the status is between 200 to 299. The video does not show how
    to do this easily.

    Project Name Status
    42900 Car 100
    42901 Bike 210
    42902 truck 250
    42903 Van 300

    I need the drop down to only show me project numbers 42901 and 42902 as they
    are the only ones where status is between 200 and 299. The list I actually have is a table about 100 rows long.

    There are several videos there; - did you watch all of the Dependant DV ones?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion



    On Wednesday, May 13, 2020 at 11:45:17 PM UTC-4, GS wrote:
    My table is set up as below. I want the data valaidation to only show projects where the status is between 200 to 299. The video does not show how
    to do this easily.

    Project Name Status
    42900 Car 100
    42901 Bike 210
    42902 truck 250
    42903 Van 300

    I need the drop down to only show me project numbers 42901 and 42902 as they
    are the only ones where status is between 200 and 299. The list I actually have is a table about 100 rows long.

    There are several videos there; - did you watch all of the Dependant DV ones?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    I did watch them but my table structure does not match the style / type they show. I created and if statement but the DV list feature will not accept it. =IF(+MID(PROJECTS[[#All],[iSTS]],1,1)="2",PROJECTS[[#All],[PROJECT NUMBER]],0)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Fri May 15 15:14:42 2020
    I did watch them but my table structure does not match the style / type they show. I created and if statement but the DV list feature will not accept it.
    =IF(+MID(PROJECTS[[#All],[iSTS]],1,1)="2",PROJECTS[[#All],[PROJECT NUMBER]],0)

    DV List only accepts range names OR hard typed lists. IOW, you need to store your lists in named ranges somewhere accessible by the DV List feature. If storing named lists on same sheet as the dropdown their scope can be sheet level. If storing named lists on a separate sheet their scope MUST be workbook level.

    However, after Zaidy036's contribution my thinking is that VBA coupled with Worksheet Event handling is required because of the analytical nature. Optionally, a menuitem could change the DV list contents effectively. This menuitem could present a dialog where you'd be able to set criteria as to what listitems to include. This would be the standard way to display data in a worksheet if using Excel for database management. Of course, the sheet to display the data would be separate from the sheet that stores the data. (Optionally, the data could be stored in a CSV and imported on demand, giving lots of flexibility as to what data is being displayed/viewed!) More complex but way more efficient overall, IMO!

    If your table is an Excel Table object OR a PivotTable then you should look at those on that same website!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Mon May 18 14:28:38 2020
    I'm a little late to the party, but! you could try a Gantt Chart style setup to show all existing projects as opposed to selecting them.

    I myself have used this tutorial link to created a handy project planner:

    https://www.youtube.com/watch?v=un8j6QqpYa0

    It has some nice functionality and scope for date range.

    HTH
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)