Count formulas between excel files

[Guest]

I have a formula linked between various excel files that performs a count
that verifies that only numbers are entered. The formula will only work when
the source file ( the one the count is being preformed on) is open. If I
update the links with the source file closed. The formula returns # value.
Is there any way to get this formula to work when the source file is closed?

=IF(COUNT('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)=COUNTA('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)+(COUNTBLANK('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)),"NO","YES")

 

[JP]

Hello,

Are you trying to get a match on the count of entries, or simply
verify that all the entries are numbers?

Based on my experience, COUNT doesn't work when the reference workbook
is closed. You can use ISNUMBER with an array and it will continue to
work when the other workbook is closed.

For example:

{=ISNUMBER('[0120PLAN.XLS]Expense by Acct'!$AI$7:$AT$7)}

returns TRUE if they are all numbers. The formula automatically
adjusts when you close the 0120PLAN workbook.


HTH,
JP

 

[Guest]

I just need to ferify that all entries are numbers, the formula you gave
works very well, thanks!

Hello,

Are you trying to get a match on the count of entries, or simply
verify that all the entries are numbers?

Based on my experience, COUNT doesn't work when the reference workbook
is closed. You can use ISNUMBER with an array and it will continue to
work when the other workbook is closed.

For example:

{=ISNUMBER('[0120PLAN.XLS]Expense by Acct'!$AI$7:$AT$7)}

returns TRUE if they are all numbers. The formula automatically
adjusts when you close the 0120PLAN workbook.


HTH,
JP

I have a formula linked between various excel files that performs a count
that verifies that only numbers are entered. The formula will only work when
the source file ( the one the count is being preformed on) is open. If I
update the links with the source file closed. The formula returns # value.
Is there any way to get this formula to work when the source file is closed?

=IF(COUNT('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)=COUNTA('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)+(COUNTBLANK('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)),"NO","YES")

 

[Guest]

I just noticed, this formula treats blanks as numbers, is there another
formula that would not count blanks or spaces as numbers ?

Hello,

Are you trying to get a match on the count of entries, or simply
verify that all the entries are numbers?

Based on my experience, COUNT doesn't work when the reference workbook
is closed. You can use ISNUMBER with an array and it will continue to
work when the other workbook is closed.

For example:

{=ISNUMBER('[0120PLAN.XLS]Expense by Acct'!$AI$7:$AT$7)}

returns TRUE if they are all numbers. The formula automatically
adjusts when you close the 0120PLAN workbook.


HTH,
JP

I have a formula linked between various excel files that performs a count
that verifies that only numbers are entered. The formula will only work when
the source file ( the one the count is being preformed on) is open. If I
update the links with the source file closed. The formula returns # value.
Is there any way to get this formula to work when the source file is closed?

=IF(COUNT('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)=COUNTA('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)+(COUNTBLANK('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)),"NO","YES")

 

[JP]

Hi,
Try this array formula:

=IF((SUM(IF(ISNUMBER(range),1,0))-COUNTA(range)-
COUNTBLANK(range))=0,"All Numbers","Not all numbers")

Replace "range" with the range of cells you were working on, ctrl-
shift-enter to complete!


--JP

I just noticed, this formula treats blanks as numbers, is there another
formula that would not count blanks or spaces as numbers ?

Hello,

Are you trying to get a match on the count of entries, or simply
verify that all the entries are numbers?

Based on my experience, COUNT doesn't work when the reference workbook
is closed. You can use ISNUMBER with an array and it will continue to
work when the other workbook is closed.

For example:

{=ISNUMBER('[0120PLAN.XLS]Expense by Acct'!$AI$7:$AT$7)}

returns TRUE if they are all numbers. The formula automatically
adjusts when you close the 0120PLAN workbook.

HTH,
JP

I have a formula linked between various excel files that performs a count
that verifies that only numbers are entered. The formula will only work when
the source file ( the one the count is being preformed on) is open. If I
update the links with the source file closed. The formula returns # value.
Is there any way to get this formula to work when the source file is closed?
=IF(COUNT('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)=COUNTA('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)+(COUNTBLANK('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)),"NO","YES")- Hide quoted text -

- Show quoted text -

 

[Guest]

The logic works, but it still contains the count formula that will not work
when the source file is closed. I will need the formula to the source file
then set up a link to that cell.

Thanks for your help. Do you know if the count formula works with a closed
workbook in Excel 2007?

Hi,
Try this array formula:

=IF((SUM(IF(ISNUMBER(range),1,0))-COUNTA(range)-
COUNTBLANK(range))=0,"All Numbers","Not all numbers")

Replace "range" with the range of cells you were working on, ctrl-
shift-enter to complete!


--JP

I just noticed, this formula treats blanks as numbers, is there another
formula that would not count blanks or spaces as numbers ?

Hello,

Are you trying to get a match on the count of entries, or simply
verify that all the entries are numbers?

Based on my experience, COUNT doesn't work when the reference workbook
is closed. You can use ISNUMBER with an array and it will continue to
work when the other workbook is closed.

For example:

{=ISNUMBER('[0120PLAN.XLS]Expense by Acct'!$AI$7:$AT$7)}

returns TRUE if they are all numbers. The formula automatically
adjusts when you close the 0120PLAN workbook.

I have a formula linked between various excel files that performs a count
that verifies that only numbers are entered. The formula will only work when
the source file ( the one the count is being preformed on) is open. If I
update the links with the source file closed. The formula returns # value.
Is there any way to get this formula to work when the source file is closed?

=IF(COUNT('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)=COUNTA('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)+(COUNTBLANK('[0120PLAN.XLS]Expense by
Acct'!$AI$7:$AT$7)),"NO","YES")- Hide quoted text -

- Show quoted text -