+ Reply to Thread
Results 1 to 6 of 6

Differentiating ACTIVE workbook name from ACTUAL workbook name

  1. #1
    Registered User
    Join Date
    08-22-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2007
    Posts
    2

    Differentiating ACTIVE workbook name from ACTUAL workbook name

    Is there a formula that will pull in the ACTUAL filename of the ACTUAL workbook, instead of the ACTIVE workbook?

    I currently use the following code to list the filename of each workbook in cell A1.

    =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

    However, when I have several similar workbooks open, I notice that this formula is not listing the ACTUAL filename, but the filename of the ACTIVE workbook. So if I hit "save" in WorkbookA, then the value of cell A1 in all other open files resets to WorkbookA. I would like cell A1 in each cell to retain its actual filename.

    I have tried resetting the workbook calculation setting from 'automatic' to 'manual', but that doesn't prevent this from happening.
    Last edited by ASumner; 08-22-2015 at 06:07 PM. Reason: typo

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655

    Re: Differentiating ACTIVE workbook name from ACTUAL workbook name

    Welcome to the board.

    The Cell function allows you to specify a range reference. See Help for the function.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    Re: Differentiating ACTIVE workbook name from ACTUAL workbook name

    As you've discovered, =CELL("filename") returns the filename of the last file changed so if you have abc.xlsx and def.xlsx open both with this formula, and you change something in abc.xlsx, the formula in def.xlsx will also show abc.xlsx.

    To avoid this, put a cell reference (any cell reference) into the formula, making your formula this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It doesn't matter what cell you choose, or what's in it.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

  4. #4
    Registered User
    Join Date
    08-22-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2007
    Posts
    2

    Re: Differentiating ACTIVE workbook name from ACTUAL workbook name

    Thank you! I remember removing the "A1" term as it was giving an error, but it works now that I put it back.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655

    Re: Differentiating ACTIVE workbook name from ACTUAL workbook name

    You're welcome.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    Re: Differentiating ACTIVE workbook name from ACTUAL workbook name

    You're welcome - glad I could help.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

+ 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. Open Another Workbook from Active Workbook but stay on Active Workbook.
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2014, 06:33 AM
  2. [SOLVED] Code to detect previous active workbook instead of current active workbook
    By kosherboy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2014, 01:58 AM
  3. Replies: 4
    Last Post: 12-01-2013, 03:22 AM
  4. VBA macro for hyperlink to active workbook in the active workbook path
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2013, 05:37 AM
  5. Open workbook, filter values, copy/paste into Active workbook.
    By niceguy21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2013, 12:17 PM
  6. [SOLVED] Edit code to close all workbooks except active workbook and other specific workbook
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2012, 09:29 PM
  7. [SOLVED] copy active sheet, create new workbook, name new workbook based on cell reference
    By jm0392 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2012, 07:12 PM

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