+ Reply to Thread
Results 1 to 6 of 6

Replace a certain part (name of excel file with another excel file) within a formula

  1. #1
    Registered User
    Join Date
    04-20-2023
    Location
    Vienna, Austria
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526)
    Posts
    2

    Lightbulb Replace a certain part (name of excel file with another excel file) within a formula

    Hi all,

    my problem is that I want to replace the name of an excel file in a formula with the name of another excel file when a certain condition is met.

    The formula is stored lets say in cell A1 and is just an XLOOKUP to retrieve figures that are stored in the "Sheet1.xlsm". Now, I want to replace the [Sheet1.xlsm] in the formula that is stored in A1 with [Sheet2.xlsm] so that the figures are retrieved from the excel file "Sheet2" when the cell J1 is starts with "02" instead of "YE" for instance.

    my approach was this:

    =IFS(LEFT(J$1,2)="YE";$X$17;LEFT(J$1,2)="02",SUBSTITUTE($X$17,"[Sheet1.xlsm]","[Sheet2.xlsm]"))

    X17 = a XLOOKUP function to retrieve figures from Sheet1 (which works).


    However, the function always returns the result for when J1 = "YE", even when J1 = "02".


    Does anyone know what the problem here is? Or is the SUBSTITUTE function just the wrong one in this case? And if so, is there another way to achieve my desired outcome?

    Hope anyone can help me in this regard.

    All the best,

    ek004

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Replace a certain part (name of excel file with another excel file) within a formula

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,749

    Re: Replace a certain part (name of excel file with another excel file) within a formula

    You cannot change formula in cell by formula in other cell.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Replace a certain part (name of excel file with another excel file) within a formula

    What the OP wants may be possible with INDIRECT, but I want to see a workbook first.

  5. #5
    Registered User
    Join Date
    04-20-2023
    Location
    Vienna, Austria
    MS-Off Ver
    Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526)
    Posts
    2

    Re: Replace a certain part (name of excel file with another excel file) within a formula

    Hi, unfortunately I cannot provide a workbook as the data is confidential.

    I also tried it using the indirect function: =IFS(LEFT(J$1,2)="YE";$X$17;LEFT(J$1,2)="02",INDIRECT(SUBSTITUTE($X$17,"[Sheet1.xlsm]","[Sheet2.xlsm]")))

    However, I get an #REF! error.

    I also read that the other excel file must be opened simultaneously for it to work. Is this correct?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Replace a certain part (name of excel file with another excel file) within a formula

    I did not ask for sensitive data. I said this:

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    A few minutes on your part preparing a sample workbook would make it far easier for us to help you.

+ 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-01-2022, 05:03 PM
  2. Replies: 7
    Last Post: 07-19-2019, 06:30 AM
  3. [SOLVED] Open excel file by providing a part of file name through inputbox
    By sbabu16 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2014, 05:30 AM
  4. Excel VBA find and replace string in non text file and rename file
    By razzack in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2013, 02:43 PM
  5. Replace part of formula file name using A1 cell value
    By trizzo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2013, 10:44 AM
  6. XML file and Excel file, Find and Replace in XML from Excel file - URGENT
    By nikythebest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-20-2012, 03:15 PM
  7. Replace Part of File Extension Before Running
    By dreicer_Jarr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2010, 11:35 AM

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