+ Reply to Thread
Results 1 to 14 of 14

List from a Table

  1. #1
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    List from a Table

    I'd like to pull a list of names from column 1 where "pst" exists in column 5.

    In this instance the list would look something like this :

    column 5
    beverly
    ginger

    jame pst pst
    beverly pst pst pst
    ginger pst pst pst

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: List from a Table

    in Column 6, enter =IF(UPPER(TRIM(B4))="PST",$A4,""). Just change the reference to B1 if your list starting from A1

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    =IFERROR(INDEX($A$2:$A$5,SMALL(IF($E$2:$E$5="pst",ROW($A$2:$SA$5)-ROW($A$2)+1),ROWS($A$1:$A1))),"")

    confirmed wit CTRL+SHIFT+ENTER not just ENTER and copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    How would it change if I was trying to look at range, say based on the instances of "pst" in both column 4 and 5?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    =IFERROR(INDEX($A$2:$A$5,SMALL(IF($D$2:$D$5="pst",IF($E$2:$E$5="pst",ROW($A$2:$SA$5)-ROW($A$2)+1)),ROWS($A$1:$A1))),"")

  6. #6
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    Can I use defined Names? Like such:

    =IFERROR(INDEX(SMCatMerchs,SMALL(IF(CycleA="pst",ROW(SMCatMerchs)-ROW('2013'!$C$18)+1),ROWS('2013'!$C$1:$C1))),"")

    ---------- Post added at 09:57 AM ---------- Previous post was at 09:55 AM ----------

    I suppose it'd be helpful to point out that I want the List to generate on a separate tab.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    Yes as long as the named ranges are all same size...

    =IFERROR(INDEX(SMCatMerchs,SMALL(IF(CycleA="pst",ROW(SMCatMerchs)-MIN(ROW(SMCatMerchs))+1),ROWS($C$1:$C1))),"")

    CSE confirmed.

  8. #8
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    Apologies. I can't seem to get this to work.

    Here's an example of what I'm trying to do :
    Attached Files Attached Files

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    Your named range for the managers is Managers... not SMCatMerchs

    Try:

    =IFERROR(INDEX(Managers,SMALL(IF(CycleA="pst",ROW(Managers)-MIN(ROW(Managers))+1),ROWS($C$1:$C1))),"")

    Also, your CycleA is 3 columns wide, so this will return the names in pst is any of those 3 columns... (assumes once max per row)

  10. #10
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    Unfortunately, I keep getting a "false" reponse.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    After entering the formula, are you holding the CTRL and SHIFT keys down, then pressing ENTER? You should see curly { } brackets appear around the formula.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    I see the brackets, but All I retrieve on the list pafe is a "false"
    Attached Files Attached Files

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: List from a Table

    You've got several formulas in one cell.

    Expand the formula bar up top and you will see... remove all except the correct one, then do the CSE confirmation... I posted a workbook in my last post.

  14. #14
    Registered User
    Join Date
    08-21-2012
    Location
    Modesto, CA
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: List from a Table

    Excellent Stuff. Thank you! Now to work on the Data Validation link and it's money.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08405 seconds
  • Memory Usage 9,252KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (14)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (14)postbit_legacy
  • (14)postbit_onlinestatus
  • (14)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./packages/vbforum/bbcodehelper/table.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1