| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count formulas between excel files
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") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count formulas between excel files
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 On Oct 19, 12:36 pm, D wrote: 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") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count formulas between excel files
I just need to ferify that all entries are numbers, the formula you gave
works very well, thanks! "JP" wrote: 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 On Oct 19, 12:36 pm, D wrote: 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") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count formulas between excel files
I just noticed, this formula treats blanks as numbers, is there another
formula that would not count blanks or spaces as numbers ? "JP" wrote: 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 On Oct 19, 12:36 pm, D wrote: 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") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count formulas between excel files
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 On Oct 19, 2:53 pm, D wrote: I just noticed, this formula treats blanks as numbers, is there another formula that would not count blanks or spaces as numbers ? "JP" wrote: 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 On Oct 19, 12:36 pm, D wrote: 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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count formulas between excel files
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? "JP" wrote: 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 On Oct 19, 2:53 pm, D wrote: I just noticed, this formula treats blanks as numbers, is there another formula that would not count blanks or spaces as numbers ? "JP" wrote: 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 On Oct 19, 12:36 pm, D wrote: 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying a Column of Formulas between Excel Files | Excel Worksheet Functions | |||
| count formulas | Excel Discussion (Misc queries) | |||
| Using count formulas with '<=' | Excel Discussion (Misc queries) | |||
| Formulas containing links to other Excel files | Excel Discussion (Misc queries) | |||
| Replicating Formulas between excel files | Excel Discussion (Misc queries) | |||
Linear Mode
