+ Reply to Thread
Results 1 to 9 of 9

Use text from cell as sheet name in formula

  1. #1
    Registered User
    Join Date
    02-13-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    19

    Use text from cell as sheet name in formula

    I am trying to re-learn excel after a long illness and I have come against a problem.

    I have tried the answers I thought might work, but I must be doing something wrong.
    I have a formula that works

    =INDEX(Sharp!$B$2:$N$212,MATCH($D$3,Sharp!$A$2:$A$212,0),1)

    I need to replace the 2 sheet names (Sharp) with the name that appears in cell B3.
    This cell (B3) uses a dropdown box to choose the manufactures name and I have each sheet labelled with the manufactures name.
    So, when I change the name in cell B3 using the dropdown I want the formula to use that sheet name.
    I am using Excel 365 and Win10.
    Sorry, if I haven't explained this very well but I'm still re-learning excel. Thanks in advance.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use text from cell as sheet name in formula

    One way:

    =INDEX(INDIRECT("'"&$B$3&"'!B2:B212"),MATCH($D$3,INDIRECT("'"&$B$3&"'!A2:A212"),0))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use text from cell as sheet name in formula

    Try

    =INDEX(INDIRECT("'"&B3 &"'!$B$2:$N$212"),MATCH($D$3,INDIRECT("'" & B3 &"'!$A$2:$A$212"),0),1)

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use text from cell as sheet name in formula

    JT... You seem to be consistently 3 minutes too slow today!!!

  5. #5
    Registered User
    Join Date
    02-13-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    19

    Re: Use text from cell as sheet name in formula

    Quote Originally Posted by Glenn Kennedy View Post
    One way:

    =INDEX(INDIRECT("'"&$B$3&"'!B2:B212"),MATCH($D$3,INDIRECT("'"&$B$3&"'!A2:A212"),0))
    Amazingly fast. Thank you very much Glenn, you saved me from going mad, It works..

  6. #6
    Registered User
    Join Date
    02-13-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    19

    Re: Use text from cell as sheet name in formula

    Quote Originally Posted by JohnTopley View Post
    Try

    =INDEX(INDIRECT("'"&B3 &"'!$B$2:$N$212"),MATCH($D$3,INDIRECT("'" & B3 &"'!$A$2:$A$212"),0),1)
    Thank you very much, this too works

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use text from cell as sheet name in formula

    @Glenn
    Got give you "old" guys a chance!!! Or more correctly, I AM just slooooow!!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use text from cell as sheet name in formula

    JT.Indeed. Feeling a bit ancient today!!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use text from cell as sheet name in formula

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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: 5
    Last Post: 07-10-2020, 04:36 AM
  2. A Formula to link 'TEXT' from cell in one sheet to another & highlight it present
    By jrobertson478 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-05-2019, 05:24 AM
  3. Replies: 2
    Last Post: 09-29-2018, 03:04 PM
  4. [SOLVED] Formula that correlates cell text to a sheet in workbook
    By the machine in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-19-2018, 11:25 AM
  5. Replies: 6
    Last Post: 01-13-2014, 08:22 PM
  6. Formula required to copy text from one sheet/cell to another
    By Jedidog1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2013, 05:56 PM
  7. Indirect Formula to return Text from one sheet to cell in another sheet
    By lou031205 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2007, 02:51 PM

Tags for this Thread

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