+ Reply to Thread
Results 1 to 6 of 6

How can i change the sheet name in the formula from the cell?

  1. #1
    Registered User
    Join Date
    11-20-2019
    Location
    nepal
    MS-Off Ver
    office 2016
    Posts
    20

    How can i change the sheet name in the formula from the cell?

    Dear all,
    I have to work in an excel file with a large number of sheets, then I have the main file where I look up the data from the different sheets using lookup formula "=index()".
    wherein this context I wanted to know if they're a possible way to select the sheet name from the cell "A1" to the formula.
    main thing is I don't want to go in every sheet to select the range in the formula.
    is there any possible way.

    To make my question clear I've uploaded the file, please check.

    regards,
    Attached Files Attached Files

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,248

    Re: How can i change the sheet name in the formula from the cell?

    You can use INDIRECT function but it is volatile and all formulas will be recalculated ever change of any cell in any open work book. Some times it takes time and looks like application hang every change.
    Last edited by BMV; 08-16-2020 at 04:27 AM.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How can i change the sheet name in the formula from the cell?

    why not all data in one worksheet?

    That is much easier to analyse.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: How can i change the sheet name in the formula from the cell?

    Define the name
    name : wk
    formular =GET.CELL(41,$B1)
    C2 cell formular =MID(wk,2,1024)

    B2 cell formular =LOOKUP(2,1/(INDIRECT("'"&$A2&"'!A:A")=B$1),(INDIRECT("'"&$A2&"'!B:B")))

    OR

    =SUMIF(INDIRECT("'"&A2&"'!A:A"),B$1,INDIRECT("'"&A2&"'!B1"))
    Last edited by wk9128; 08-16-2020 at 05:20 AM.

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,248

    Re: How can i change the sheet name in the formula from the cell?

    GET.CELL is macro function. The worsheet name could be get by regular CELL function.
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: How can i change the sheet name in the formula from the cell?

    get worksheet name

    You can refer to the upper floor BMV method or this method

    Define the name
    name : shtname
    formular =replace(get.document(1),1,find("]",get.document(1)),)&t(now())
    any cell formular then key in =shtname

+ 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: 01-15-2016, 06:16 PM
  2. change cell colour for all cells in sheet containing formula's
    By Robert1311 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-18-2015, 09:17 AM
  3. Replies: 1
    Last Post: 10-30-2012, 08:51 PM
  4. Replies: 3
    Last Post: 10-04-2012, 02:49 PM
  5. Change Cell Formula from master sheet
    By DAVIDJMORIN in forum Excel General
    Replies: 1
    Last Post: 05-22-2011, 03:18 PM
  6. Change the work sheet name in a formula by using cell reference
    By Neel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2006, 04:25 AM
  7. Change workbook sheet reference using cell A1 to change a vairable
    By Reed in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2005, 04:06 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