+ Reply to Thread
Results 1 to 7 of 7

automatically copy items from a changing list into a sortable table

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    8

    automatically copy items from a changing list into a sortable table

    Hi,

    I have a list (formatted as a table) on one sheet that I want users to be able to change. On another sheet there is a table that needs to have every item from the list in the first column. So far I have achieved this by using "=Table2[[#This Row],[Crop List]]" in every cell of the column where I want the list copied. ("Table2" is the list, "Crop List" is the header on the list.) This basically works, but it has some drawbacks:

    1) When I try to sort the table, the items referencing the list are stuck in their original positions and no longer match the other columns.

    2) I have to have a large number of cells displaying "#VALUE!" at the bottom of my table because they refer to empty cells in the list which might later be filled.

    Any suggestions?

    In case my explanation wasn't clear, I've attached a copy of the workbook. Everything I'm talking about is on the first two sheets, "Lists" and "Pricing."

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: automatically copy items from a changing list into a sortable table

    Hi illyBLC

    I think that your problem is that you are using the sheets for the wrong things. I'm not sure why you want the "list" sheet, but your pricing sheet should not be dependent on another sheet - the pricing sheet shoulod be the basis of your other sheets.

    I would make the pricing sheet into 1 table, so that if you sort on 1 column, the other columns move with it.

    To get rid of the "#VALUE!" (which you will not have if you follow my advice above ! ), wrap your formula in an "IFERROR" statement as in
    Please Login or Register  to view this content.
    which, in English is "value from Table2,Crop list unless that gives an error, in which case "" (nothing)"

    Hope this helps

    Regards
    Alastair

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automatically copy items from a changing list into a sortable table

    Hi,

    In case you were wondering, I want the "List" sheet because those lists are referenced in a number of other places, not only the Pricing sheet. My problem with the Pricing sheet was only one example of a problem that I have been dealing with throughout the workbook.

    That being said, this IFERROR thing sounds promising.

    Thanks,
    Bill

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: automatically copy items from a changing list into a sortable table

    Hi Bill

    I understand needing to be able to reference elsewhere. If you have the pricing as your base, you will not get the problem 1) not matching other columns as there is no relevant column on the "list".

    Perhaps you could reference to "pricing" and do away with the Crop list part of list? Even if you can't at least you will not have the mis-matching problem.

    Let me know if I can assist further.

    Regards
    Alastair

  5. #5
    Registered User
    Join Date
    10-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automatically copy items from a changing list into a sortable table

    Hi Alastair,

    I had thought about doing away with the Crop List, or in effect using the first column of the pricing table as the crop list. The drawback of that plan would be that I still have the reference problem elsewhere in the workbook, for example on the "Summary" sheet. I also have a similar problem where the Task List is referenced on the Crop Report sheet, and so on. I am starting to think that there is no perfect solution to this problem.

    I've changed the Crop column in the pricing table to simple references like "=Lists!A2," which helps with the sorting problem. Now, though, if an item is added to the Crop List the user has to copy that formula down to a new cell in the Pricing table. If I leave some empty cells at the bottom of the Pricing Table in case of an addition to the Crop List, then I have 0's there, which I don't like. I think that's the best I can do, though.

    Meanwhile, IFERROR is helping things.

    Cheers,
    Bill

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: automatically copy items from a changing list into a sortable table

    Hi Bill

    You are probably right in that there is no perfect solution!
    However, it is very easy to suppress 0s - set the number formatting (on the Home tab) to Custom:
    Please Login or Register  to view this content.
    which means the formatting for [positive];[negative];[zero];[Alpha]. You will note that there is nothing between the 2nd and 3rd semicolons ( so zeros are suppressed. Job done.

    Regards
    Alastair

  7. #7
    Registered User
    Join Date
    10-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automatically copy items from a changing list into a sortable table

    That's a neat trick.

+ 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. Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  2. Payroll Calculations using a Sortable Table
    By .h2o. in forum Excel General
    Replies: 4
    Last Post: 07-12-2012, 02:44 PM
  3. Excel 2007 : Automatically counting items in a list
    By Matt Neuteboom in forum Excel General
    Replies: 7
    Last Post: 09-17-2010, 11:07 AM
  4. Sortable list of checkboxes
    By nvtd1 in forum Excel General
    Replies: 0
    Last Post: 12-19-2005, 06:10 PM
  5. sortable list
    By Greg in forum Excel General
    Replies: 2
    Last Post: 04-10-2005, 10:06 AM

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.06765 seconds
  • Memory Usage 9,029KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (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
  • (2)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (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 (39):
  • ./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 

Hooks Called (49):
  • 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_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1