+ Reply to Thread
Results 1 to 13 of 13

Excel Formula Doesn't work when Source File is closed

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Excel Formula Doesn't work when Source File is closed

    Hi All,

    I have follwoing formula which calculates number of filled cells in last 10 rows of column X.
    I am using column Y because it will have all the cells filled up always.

    =COUNTA(INDIRECT("'D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!X" & COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)-7 & ":X" & COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)+2))

    The formula works perfect when the other file (File on which I am running the formula) is opened. But gives wrong results when it is closed.

    Can anybody please help me with this?

    Thanks,
    Tejas

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Excel Formula Doesn't work when Source File is closed

    Indirect will not work with closed workbooks

    Make use of Indirect.Ext of Morefuc addin


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Excel Formula Doesn't work when Source File is closed

    Ok thank you Sixthsense

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Excel Formula Doesn't work when Source File is closed

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  5. #5
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Excel Formula Doesn't work when Source File is closed

    I will wait for few more hours as I am expecting one other solution as well.
    Hope that is fine!

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Excel Formula Doesn't work when Source File is closed

    Quote Originally Posted by :) Sixthsense :) View Post
    Indirect will not work with closed workbooks

    Make use of Indirect.Ext of Morefuc addin
    Do you have a link ? I can't seem to find it any more

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Excel Formula Doesn't work when Source File is closed

    Quote Originally Posted by Pepe Le Mokko View Post
    Do you have a link ? I can't seem to find it any more
    Here it is....

    excelenthusiasts.sharepoint.com/Documents/Morefunc.zip

    The source article link...
    http://www.ashishmathur.com/tag/morefunc/

  8. #8
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel Formula Doesn't work when Source File is closed

    You may try:
    =COUNTA(INDEX('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!X:X,COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)-7):INDEX('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!X:X,COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)+2))
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  9. #9
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Excel Formula Doesn't work when Source File is closed

    @Izandol:
    Still same issue. Formula gives wrong result when the file is closed.
    Do you need the file to solve this issue?

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Excel Formula Doesn't work when Source File is closed

    @Sixthsense - Thank you

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel Formula Doesn't work when Source File is closed

    Quote Originally Posted by Tejas.T View Post
    @Izandol:
    Still same issue. Formula gives wrong result when the file is closed.
    Without testing I assumed that Izandol's solution would work because INDEX can work with closed workbooks.....but I think that the specific INDEX syntax used, i.e. INDEX_formula:INDEX_formula, to give a range is still partially volatile and so won't work with a closed workbook, OFFSET is also out for the same reason.

    Try using an "array formula" like this:

    =SUM(IF(ROW('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!$X:$X)>MAX(IF('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!$Y:$Y<>"",ROW('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!$Y:$Y)))-10,IF('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!$X:$X<>"",1)))

    confirmed with CTRL+SHIFT+ENTER

    that works for me whether the workbook is open or closed
    Audere est facere

  12. #12
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel Formula Doesn't work when Source File is closed

    It did work for me with 2013 but I have only tested quickly.

  13. #13
    Registered User
    Join Date
    11-30-2013
    Location
    United states
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Excel Formula Doesn't work when Source File is closed

    To reference a closed file to count ,
    =SUMPRODUCT(--'\\<yourpath>\[Testing1.xlsx]Sheet1'!$A$1:$A$10)

    This formula would work, however, if the source file is moved the return values are not consistent.

    But if your source file if going to be in the loaction then this formula should work.

    Quote Originally Posted by Tejas.T View Post
    Hi All,

    I have follwoing formula which calculates number of filled cells in last 10 rows of column X.
    I am using column Y because it will have all the cells filled up always.

    =COUNTA(INDIRECT("'D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!X" & COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)-7 & ":X" & COUNTA('D:\Hard CEP\[Nadal Rafael @ hard.xlsx]Total'!Y:Y)+2))

    The formula works perfect when the other file (File on which I am running the formula) is opened. But gives wrong results when it is closed.

    Can anybody please help me with this?

    Thanks,
    Tejas

+ 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. How to use OFFSET formula when the source file is closed
    By chavanalini in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-22-2013, 02:30 AM
  2. Concentate formula doesn't work on new excel.
    By rysiu453 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2011, 03:27 PM
  3. How to update destination file with source files closed?
    By Alex Costache in forum Excel General
    Replies: 2
    Last Post: 08-01-2006, 05:10 AM
  4. Replies: 4
    Last Post: 05-12-2006, 02:00 PM
  5. [SOLVED] Excel 97 - Adding Every 8th Row - Formula should work, but doesn't
    By Damaeus in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-23-2005, 01: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