+ Reply to Thread
Results 1 to 13 of 13

Excel Formula Doesn't work when Source File is closed

  1. 02-04-2014, 12:38 AM #1
    Tejas.T
    Tejas.T is offline
    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
    Reply With QuoteRegister To Reply
  2. 02-04-2014, 12:56 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    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
    Reply With QuoteRegister To Reply
  3. 02-04-2014, 01:46 AM #3
    Tejas.T
    Tejas.T is offline
    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
    Reply With QuoteRegister To Reply
  4. 02-04-2014, 01:52 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    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.
    Reply With QuoteRegister To Reply
  5. 02-04-2014, 03:09 AM #5
    Tejas.T
    Tejas.T is offline
    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!
    Reply With QuoteRegister To Reply
  6. 02-04-2014, 03:42 AM #6
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,621

    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
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  7. 02-04-2014, 03:58 AM #7
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    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/
    Reply With QuoteRegister To Reply
  8. 02-04-2014, 04:48 AM #8
    Izandol
    Izandol is offline
    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
    Reply With QuoteRegister To Reply
  9. 02-04-2014, 08:48 AM #9
    Tejas.T
    Tejas.T is offline
    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?
    Reply With QuoteRegister To Reply
  10. 02-04-2014, 08:58 AM #10
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,621

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

    @Sixthsense - Thank you
    Reply With QuoteRegister To Reply
  11. 02-04-2014, 09:29 AM #11
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,720

    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
    Reply With QuoteRegister To Reply
  12. 02-04-2014, 10:02 AM #12
    Izandol
    Izandol is offline
    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.
    Reply With QuoteRegister To Reply
  13. 07-30-2014, 02:38 PM #13
    ExcelNewbie5
    ExcelNewbie5 is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. INDIRECT function do not work when other file is closed
    By starguy in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1