+ Reply to Thread
Results 1 to 4 of 4

Using Text in another cell to use as part of a formula

  1. #1
    Registered User
    Join Date
    05-09-2016
    Location
    New Zealand
    MS-Off Ver
    7
    Posts
    1

    Using Text in another cell to use as part of a formula

    Hi all,

    This might have been answered in a previous thread but I can't seem to find it!

    I am wanting to return a value in cell F33 of Sheet 1, Sheet 2, Sheet 3...... Sheet 40 (and future sheets added) to a Summary sheet. I have created a macro which will create a hyperlink in the summary sheet to any new sheet created with the name of the new sheet.

    I want a formula like ='Sheet1'!F33 however I don't want to manually type each name of a new sheet as this will be time consuming. I would rather ='*hyperlink*!F33 so that the formula can be replicated multiple times.

    Any help would be much appreciated!

    Thanks team!!

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Using Text in another cell to use as part of a formula

    EDIT
    This is not what you wanted - got sidetracked when I saw the word "hyperlink"
    The formula you requested is in post#4


    Welcome to the forum.
    It depends where you want to put the links. This is an example that may point you in the right direction

    The attached worksheet contains a summary sheet followed by 5 other sheets
    The macro counts the number of sheets in the workbook (=6) and creates links in cells A2 to A6 in the summary sheet to cell F33 in each other sheet.
    In any workbook, sheets have the tab names ("Summary", "Sheet1" etc) but can also be referred to by their index number (here Sheets(1) to Sheet(6)). This macro uses their index number.
    Sheet "Summary" is Sheets(1) - so we need links to Sheets(2) to Sheets(6)
    The links are placed in cells A2 to A6 - so the row number of the cell containing the link is the same as the sheet index number.

    {CTRL} + q runs the macro in the attached workbook

    Please Login or Register  to view this content.
    EDIT
    This is not what you wanted - got sidetracked when I saw the word "hyperlink"
    The formula you requested is in post#4
    Attached Files Attached Files
    Last edited by Kevin#; 05-10-2016 at 01:40 AM. Reason: Not what was requested!
    If a response has helped then please consider rating it by clicking on *Add Reputation below the post
    When your issue has been resolved don't forget to mark the thread SOLVED (click Thread Tools at top of thread)

  3. #3
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Using Text in another cell to use as part of a formula

    Welcome to the Forum Kelpappreciated,

    If I understated your requirements correctly, and assuming you have the Sheet Name in A3 and following down the sheet, you could use a formula like this in, say B3: =INDIRECT(A3&"!F33")

    This will give you the value from the named sheet.

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Using Text in another cell to use as part of a formula

    In post#2 I included a hyperlink to F33 in each sheet instead of the actual formula you requested!
    This is the "formula" version
    {CTRL} + q runs the macro in the attached workbook

    The attached worksheet contains a summary sheet followed by 5 other sheets
    The macro counts the number of sheets in the workbook (=6) and creates formula in cells A2 to A6 in the summary sheet
    In any workbook, sheets have the tab names ("Summary", "Sheet1" etc) but can also be referred to by their index number (here Sheets(1) to Sheet(6)). This macro uses their index number.
    Sheet "Summary" is Sheets(1) - so we need values (in F33) from Sheets(2) to Sheets(6)
    (Values are placed in cells A2 to A6 - so the row number of the cell containing the formula is the same as the sheet index number)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Kevin#; 05-10-2016 at 01:46 AM.

+ 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. Formula to move part of a value to a new cell - part 2
    By forestavekids in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-28-2016, 12:34 PM
  2. Formula to produce result based if part of text in one cell matches another
    By @MeDaveT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2015, 10:11 AM
  3. Formula to identify part of a text in cell from range of cells & insert adjacent cell text
    By Novicebutnotforlong in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-19-2013, 02:11 AM
  4. Replies: 5
    Last Post: 04-08-2012, 01:06 AM
  5. Replies: 8
    Last Post: 03-13-2012, 01:04 PM
  6. Replies: 13
    Last Post: 11-05-2011, 03:00 PM
  7. Insert cell text as part of formula?
    By elazarus in forum Excel General
    Replies: 3
    Last Post: 06-03-2009, 11:24 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.05942 seconds
  • Memory Usage 8,977KB
  • 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
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)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_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • 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