+ Reply to Thread
Results 1 to 8 of 8

Selectable columns for display.

  1. #1
    Registered User
    Join Date
    07-16-2020
    Location
    London, England
    MS-Off Ver
    2010 Pro
    Posts
    4

    Selectable columns for display.

    I'm probably using the wrong terminology, so please bear with me. I do welcome corrections though! That way I may get it right next time.
    We have on our website a downloadable excel comparison table of differences in safeguarding legislation across England/Scotland/Wales/Northern Ireland/Southern Ireland/Jersey/Guernsey etc. Most people who need it are only trying to compare two areas - e.g. what are the differences in Safeguarding legislation in England and Scotland? Trying to do that with columns at opposite ends of the table is almost impossible!
    Is it possible to set up a downloadable excel document so that they can choose (using say, a drop down list) just the two or three columns they need?
    I know how to select/hide/etc columns - we need something simpler to use than that which does not rely on knowledge of how excel works.
    By the way, our organisation is not commercial - it's a community interest company dedicated to improving safeguarding for children and adults at risk wordwide.

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Selectable columns for display.

    If you upload an example it will be easier.
    You can have a tab - that has data validation lists of the countries in column B & C in row 1 for example (I am guessing col A will be the header)
    Then Index find to fill it out the data =If($A$1<>"",Index(DataTab!$A$1:$Z$20,Row(),Match($A$1,DataTab!$A$1:$Z$1,0)),"")

  3. #3
    Registered User
    Join Date
    07-16-2020
    Location
    London, England
    MS-Off Ver
    2010 Pro
    Posts
    4

    Re: Selectable columns for display.

    Thanks CRIMEDOG, I'll give it a go!
    This is the page I'm referring to, minus the hyperlinks. If an organisation works in say Ireland and Aldernay, I want them to be able to choose to display only those columns. Can be more than two areas needed.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Selectable columns for display.

    Try this - Data validation rule: List in row 1
    Index Match below - Note the change for each column : Match($B$1 - Match($C$1 etc...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-16-2020
    Location
    London, England
    MS-Off Ver
    2010 Pro
    Posts
    4

    Re: Selectable columns for display.

    Hi CRIMEDOG
    Much appreciated. Not come across Array before (I'm self taught as far as it goes!) - useful. I reckon I can tailor this for what I need - thanks.
    The array formula in B2 is {=IF(OR($B$1="",$B$1="(Choose One)"),"",INDEX(DATA!$B$1:$I$12,ROW(),MATCH($B$1,$B$1:$I$1,0)))}
    I can cope with most of it, but don't understand (Choose One). How does that work? I assume it's something to do with the data validation list?
    Cheers
    Mike

  6. #6
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Selectable columns for display.

    It is not an array. The Choose one is just the first option in the list Look at Data!A1:I1 A1 is (Choose One). If that option is showing I would want the formula to return blank (and I would want the option to show so user would know what to do)
    The formula will work without the {} brackets.

  7. #7
    Registered User
    Join Date
    07-16-2020
    Location
    London, England
    MS-Off Ver
    2010 Pro
    Posts
    4

    Re: Selectable columns for display.

    Hi CRIMEDOG
    (Choose One); Obvious now you point it out - sorry for being a bit slow. Makes sense.
    Array: I noticed the {} around your formulae which vanished when I tried to alter/extend. I looked them up and discovered Array/CSE - dead useful for the future.
    Thanks again.
    Cheers
    Mike

  8. #8
    Registered User
    Join Date
    08-31-2020
    Location
    MA
    MS-Off Ver
    Windows 10 and 2016
    Posts
    6

    Re: Selectable columns for display.

    Thanks for the info, this helped me out!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Display columns in excel based on Data validation list in multiple columns
    By tejboyd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2017, 09:17 PM
  2. Replies: 2
    Last Post: 07-25-2016, 01:27 AM
  3. [SOLVED] Match 2 columns and display options in the third column that matches the 2 columns
    By SBBmaster09 in forum Excel - New Users/Basics
    Replies: 18
    Last Post: 04-23-2013, 06:51 AM
  4. Find and display the last occurance in two columns and display related cell
    By willia97 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2012, 08:14 PM
  5. VBA match index, display multiple values with selectable reference file path
    By Undru in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-29-2011, 06:25 AM
  6. Pivot Table: Columns not selectable as fields
    By Oaki in forum Excel General
    Replies: 1
    Last Post: 09-15-2009, 07:55 PM
  7. Selectable list box
    By mkmed in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-29-2007, 02:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1