+ Reply to Thread
Results 1 to 11 of 11

creating an interactive spreadsheet

  1. #1
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Question creating an interactive spreadsheet

    Hello Everyone!

    First, I would like to apologize if I'm posting this in the wrong section of the forum.

    Having said that, I'm taking on a new project in which I need to create an interactive spreadsheet that will eventually be converted for the web. How I'm going to achieve the latter is something I'm not concerned with at the moment. What does concern is how this spreadsheet will come to be.

    Unfortunately I'm not an Excel expert, otherwise I wouldn't have ended up here. But I am a fast learner so I hope to learn a lot from knowledgeable people on this site

    By the way, I searched the web quite a bit to find a solution but there was a lot of information and I wasn't sure what method to apply.

    At any rate, what I'm trying to achieve is simple to explain. Let me give you an example:

    > I have a list of cell phones.
    > Each cell phone will provide three pieces of information: what carrier, how much storage space, and how many there are in stock.
    > The cell phones should display in a list.
    > Upon selecting a cell phone, the row should auto fill with the respective information of each cell phone (assuming across three columns).

    So if someone from the list chooses HTC HD2, the results should be displayed in the following manner:

    B2: HTC HD2 > C2: T-Mobile > D2: 16 GB > E2: 26

    I hope I have made this clear enough.

    I have seen people talk about several ways of doing this but I'm not sure how and what to implement. Do I bring Access in the mix? Do I use VBA? I just don't know how to get started.

    I hope you guys can help!
    Last edited by cl0ne; 03-02-2011 at 06:30 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Help with creating an interactive spreadsheet

    Is this is an Excel Spreadsheet, not an Access Database?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Help with creating an interactive spreadsheet

    Thanks for the fast response.

    That's a good question. Should I be considering Access to make this? Do I need both?

    Thanks again.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Help with creating an interactive spreadsheet

    No you've posted in the Access forum,I'll move it for you

  5. #5
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: creating an interactive spreadsheet

    Okay.

    Thanks for the move!

    I just hope now someone can help with tackling my project!

    Thanks again!

  6. #6
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    728

    Re: creating an interactive spreadsheet

    Use vlookup function

    Assuming Phone is entered in A8
    Model will be populated in B8
    Storage will be populated in C8
    Storage left will be populated in D8

    Your source range is E3 to H100
    use this formula

    Please Login or Register  to view this content.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  7. #7
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: creating an interactive spreadsheet

    Thank you for the reply mahju.

    Concise is what I was looking for and that's what you gave me!

    I have made a note of your solution. However, can you tell me how this will tie in with the list of phones I will create? I need to be able to select a phone from the list and upon selection the relevant information should be filled out automatically in the adjacent cells.

    Do I need to make tables in excel of all the data used? Should I use Access for tables and then call upon them from excel? Do I have to order my data in a specific way?

    I'm really trying to build this from ground up.

    I appreciate all the help

  8. #8
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: creating an interactive spreadsheet

    Can anyone provide more tips on how to make this work?

    Thanks

  9. #9
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    728

    Re: creating an interactive spreadsheet

    Make a list of your phone number and apply this formula

    To make a drop down list use Data validation
    check this for data validation explanation

    http://www.excelforum.com/excel-gene...r-message.html

    see attached sheet for explanation

    Thanks

    Regards
    Attached Files Attached Files
    Last edited by mahju; 03-03-2011 at 06:37 AM.

  10. #10
    Registered User
    Join Date
    03-02-2011
    Location
    U.S.A
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: creating an interactive spreadsheet

    Excellent!

    Worked perfectly! I just had to understand the formula and how you put it together.

    Thanks a lot! Just what I was looking for!

  11. #11
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    728

    Re: creating an interactive spreadsheet

    Vloomup function has 4 arguments


    vlookup(lookup value, table array, col. index number , range_lookup)

    lookup value is the value you want to look (Phone in this case. Use of $ is explained 2nd argument)

    table array is the range of your data ($E$4 to $H$6)
    (Use $ to fix this range (Absolute reference)otherwise it will be changed as copied down)

    col. index number Column number in which is the data you want to search (2 for carrier, 3 for storage and 4 for left in sample sheet)

    range_lookup Its value is "false" to have exact mach (This is in your case)

    Hope this will do

    If you are satisfied mark this thread as solved

    Regards
    Last edited by mahju; 03-07-2011 at 09:53 AM.

+ 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.07129 seconds
  • Memory Usage 9,105KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (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)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (11)postbit_legacy
  • (11)postbit_onlinestatus
  • (11)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 (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 (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