+ Reply to Thread
Results 1 to 3 of 3

How to use a cell value as part of a workbook reference, in a formula?

  1. #1
    Registered User
    Join Date
    11-23-2012
    Location
    Halden
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to use a cell value as part of a workbook reference, in a formula?

    Hi,

    It is probably a very simple solution to this problem. Have tried to search for it, but haven't found any, and is hoping that someone is able to help with the problem

    I have this formula:
    =IF(A2&B2="";;SUMPRODUCT(1*('G:\Shared\3_HMS and Kvalitet (QC)\3.5_DM\[Dokumentkontroll.xlsm]Registration'!CI:CI=A2&B2))+SUMPRODUCT(1*('G:\Shared\3_HMS and Kvalitet (QC)\3.5_DM\[Dokumentkontroll.xlsm]Returned documents'!P:P=A2&B2)))

    What i want to do is to change the first part of the workbook reference, this part:
    G:\Shared\3_HMS and Kvalitet (QC)\3.5_DM\

    With text that is stored in a cell, say L2. This way i will be able to make the workbook reference dynamic, so i don't have to repair broken links, when someone desides to change a folder name.

    Any help will be appreciated

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to use a cell value as part of a workbook reference, in a formula?

    You would normally use the INDIRECT function for this. However, it does not work with closed workbooks. If you are allowed to download add-ins, there is a free one called morefunc which has the extra function INDIRECT.EXT, and this will work with closed workbooks - do a Google search on places to download it.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-23-2012
    Location
    Halden
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to use a cell value as part of a workbook reference, in a formula?

    Hi, Pete

    Thank you for the response INDIRECT is not an option since i need it to work with a closed workbook and sadly i'm not allowed to install any add-ins so INDIRECT.EXT is also not an option.

+ 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. Reference a specific cell for part of workbook name in formula
    By randolphoralph in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-04-2013, 01:09 PM
  2. [SOLVED] Trying to reference a cell in another workbook as part of a formula in VBA
    By DPWM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2013, 06:21 PM
  3. Replies: 4
    Last Post: 11-05-2012, 07:56 PM
  4. using a cell reference as part of a formula
    By luca in forum Excel General
    Replies: 1
    Last Post: 10-07-2009, 06:23 AM
  5. How to find sheet in formula by (part) cell reference. use Indirect ???
    By KrisVeen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-21-2006, 06:38 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