| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How do I correct a #value! error
I have a somewhat complicated formula in that it refers to a separate
spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
|
#2
|
|||
|
|||
|
Hi
as always: please post your formula -- Regards Frank Kabel Frankfurt, Germany "How do I eliminate a #value! error?" <How do I eliminate a #value! schrieb im Newsbeitrag ... I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
|
#3
|
|||
|
|||
|
Thanks Frank. Here it is:
=(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 "Frank Kabel" wrote: Hi as always: please post your formula -- Regards Frank Kabel Frankfurt, Germany "How do I eliminate a #value! error?" <How do I eliminate a #value! schrieb im Newsbeitrag ... I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
|
#5
|
|||
|
|||
|
I tried inputting the formula you suggested and I get a #Num! error. I have
never used SUMPRODUCT before. Does the formula you suggest only multiple column G by .7/3 when column S has a three in it? Is there any additional suggestions you may have as to the changing of my approach to this problem? "Frank Kabel" wrote: Hi SUMIF won't work on closed workbooks. If you open a file saved in an older version Excel 2002/2003 to re-calculate all cells (and fails with that). Workaround: Use SUMPRODUCT instead. e.g. for your formula: =(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 -- Regards Frank Kabel Frankfurt, Germany "Terri Miller" <Terri schrieb im Newsbeitrag ... Thanks Frank. Here it is: =(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 "Frank Kabel" wrote: Hi as always: please post your formula -- Regards Frank Kabel Frankfurt, Germany "How do I eliminate a #value! error?" <How do I eliminate a #value! schrieb im Newsbeitrag ... I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
|
#6
|
|||
|
|||
|
Hi
sorry, my fault. I just copied your formula. SUMPRODUCT can't work with ranges such as $S:$S. So change the formula to: =(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G1:$G1000)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P1:$P1000))/3)*0.7 For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Terri Miller" schrieb im Newsbeitrag ... I tried inputting the formula you suggested and I get a #Num! error. I have never used SUMPRODUCT before. Does the formula you suggest only multiple column G by .7/3 when column S has a three in it? Is there any additional suggestions you may have as to the changing of my approach to this problem? "Frank Kabel" wrote: Hi SUMIF won't work on closed workbooks. If you open a file saved in an older version Excel 2002/2003 to re-calculate all cells (and fails with that). Workaround: Use SUMPRODUCT instead. e.g. for your formula: =(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 -- Regards Frank Kabel Frankfurt, Germany "Terri Miller" <Terri schrieb im Newsbeitrag ... Thanks Frank. Here it is: =(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 "Frank Kabel" wrote: Hi as always: please post your formula -- Regards Frank Kabel Frankfurt, Germany "How do I eliminate a #value! error?" <How do I eliminate a #value! schrieb im Newsbeitrag ... I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
|
#7
|
|||
|
|||
|
Thanks!
Terri "Frank Kabel" wrote: Hi sorry, my fault. I just copied your formula. SUMPRODUCT can't work with ranges such as $S:$S. So change the formula to: =(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G1:$G1000)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P1:$P1000))/3)*0.7 For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Terri Miller" schrieb im Newsbeitrag ... I tried inputting the formula you suggested and I get a #Num! error. I have never used SUMPRODUCT before. Does the formula you suggest only multiple column G by .7/3 when column S has a three in it? Is there any additional suggestions you may have as to the changing of my approach to this problem? "Frank Kabel" wrote: Hi SUMIF won't work on closed workbooks. If you open a file saved in an older version Excel 2002/2003 to re-calculate all cells (and fails with that). Workaround: Use SUMPRODUCT instead. e.g. for your formula: =(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 -- Regards Frank Kabel Frankfurt, Germany "Terri Miller" <Terri schrieb im Newsbeitrag ... Thanks Frank. Here it is: =(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMIF('C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7 "Frank Kabel" wrote: Hi as always: please post your formula -- Regards Frank Kabel Frankfurt, Germany "How do I eliminate a #value! error?" <How do I eliminate a #value! schrieb im Newsbeitrag ... I have a somewhat complicated formula in that it refers to a separate spreadsheet. The formula works fine, but the problem seems to be that until I open the referred to spreadsheet, I get a #value! error. I know that I have used this type of formula before and didn't need to open the spreadsheet to get my answers. Why is it doing it now? It might help to let you know that the spreadsheet referred to was created by access as a query, but I don't see how that changes it as I save the query as an excel spreadsheet. Any help would be greatly appreciated, as the spreadsheet generated refers to 7 different files (Sunday - Monday) and I need to creat a new spreadsheet for 8 different locations, so I would have to open over 56 files to get these spreadsheets to the stores. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Findlink Error | Links and Linking in Excel | |||
| Error message opening Excel97 workbook | Excel Discussion (Misc queries) | |||
| replace error by value | Excel Discussion (Misc queries) | |||
| How to set Error Bars to _two_ SDs? | Charts and Charting in Excel | |||
| Device I/O Error | Excel Discussion (Misc queries) | |||
Linear Mode
