+ Reply to Thread
Results 1 to 6 of 6

Formula to reference cells from a closed workbook?

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Formula to reference cells from a closed workbook?

    Hello,

    I have an inventory database that I have used for a long time with no problems until today.
    There are 2 columns in the Database that pull from separate workbooks to obtain their values. One uses a SUMIF and the other uses SUMPRODUCT, but throughout the day both have separately broken while trying to fix other things in the excel files that are referenced.
    They both come up as "#VALUE!"

    As of right now, the column that is not working is the one using the SUMIF formula.

    I have attached a picture of the layout of the workbook (the green columns are usually where it says #Value!).
    Column E's formula:
    Please Login or Register  to view this content.
    Column F'S formula:
    Please Login or Register  to view this content.
    Is there a different method I could go about to reference the cells values?

    Any help is greatly appreciated!
    Miranda

    example1.PNG

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Formula to reference cells from a closed workbook?

    Dear friend, if you attach sample file with desire result its more helpful both.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to reference cells from a closed workbook?

    The formulas look right. Has the network path changed perhaps?
    Try referencing just 1 cell from 1 of those workbooks, eg...
    ='V:\Team\CC60128310\Common\05_Inventory\[Inventory_List_2018.xlsm]Log_Customer Orders'!$C$4

    See what that returns. Then manually make the same link, using the mouse to point to that cell (as you would normally do when constructing a formula).
    Compare the paths, are they identical?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Formula to reference cells from a closed workbook?

    Unfortunately after correcting the link it is still having problems pulling the values.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Formula to reference cells from a closed workbook?

    It is the same exact line of code from before in the same location. Now it is acting like the link doesn't work and when I click on the formula it wants me to relink the Inventory Order Log to the workbook and then when I do so it switches to #Value!
    I am not sure why

  6. #6
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Formula to reference cells from a closed workbook?

    I figured it out on my own. The columns it was pulling from ($_$4:$_$1000) was what was causing the error because in that excel sheet the formulas entered stops at 718, so the way to fix it would be to fix the size to ($_$4:$_$718) or extend the formula in that workbook down to 1000.

    Thanks anyway!

+ 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. Dynamic closed workbook reference
    By a1b2c3d4e5f6g7h8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2017, 10:22 AM
  2. I need to reference to another workbook that is closed.
    By pasqualebaldi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2016, 03:00 PM
  3. How to reference closed workbook
    By excelnewbie716 in forum Excel General
    Replies: 6
    Last Post: 10-08-2015, 12:06 PM
  4. Hard-coding formula to reference cell from closed workbook
    By ThomServo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2015, 02:51 PM
  5. Cell reference a workbook even when workbook is closed
    By suban.p in forum Excel General
    Replies: 2
    Last Post: 11-04-2014, 12:08 PM
  6. Use cell value as reference to closed workbook?
    By ChrisKustom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-17-2013, 12:32 AM
  7. Indirect reference to closed workbook
    By Dingo0z in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-19-2008, 01:20 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