Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
I need the sheet to reference another file and update accordingly. I have
entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
There are some functions that don't work with closed workbooks:
=indirect(), =sumif(), =countif() are a few shakey1181 wrote: I need the sheet to reference another file and update accordingly. I have entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
is there anyway around this? [i am using =countif()]
"Dave Peterson" wrote: There are some functions that don't work with closed workbooks: =indirect(), =sumif(), =countif() are a few shakey1181 wrote: I need the sheet to reference another file and update accordingly. I have entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
You can replace your formula with =sumproduct().
=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3)) Adjust the range to match--but you can't use the whole column. If you create the formula with the book1.xls workbook open, you may find it easier. Excel will adjust the formula when you close that workbook. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html shakey1181 wrote: is there anyway around this? [i am using =countif()] "Dave Peterson" wrote: There are some functions that don't work with closed workbooks: =indirect(), =sumif(), =countif() are a few shakey1181 wrote: I need the sheet to reference another file and update accordingly. I have entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
Still returning #VALUE!.
The COUNTIF formula, that returned the correct data was as follows: =COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10) this showed the correct data [count of B10 entires] if either both sheets were open or you didn't update. using SUMPRODUCT I have written the below: =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)) this still returns an error, any thoughts? "Dave Peterson" wrote: You can replace your formula with =sumproduct(). =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3)) Adjust the range to match--but you can't use the whole column. If you create the formula with the book1.xls workbook open, you may find it easier. Excel will adjust the formula when you close that workbook. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html shakey1181 wrote: is there anyway around this? [i am using =countif()] "Dave Peterson" wrote: There are some functions that don't work with closed workbooks: =indirect(), =sumif(), =countif() are a few shakey1181 wrote: I need the sheet to reference another file and update accordingly. I have entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing other sheets - updates
=SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27=$B10))
Notice that the last comma is now an equal sign. shakey1181 wrote: Still returning #VALUE!. The COUNTIF formula, that returned the correct data was as follows: =COUNTIF('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10) this showed the correct data [count of B10 entires] if either both sheets were open or you didn't update. using SUMPRODUCT I have written the below: =SUMPRODUCT(--('C:\[01.01.06.XLS]Sheet1'!$E$7:$G$27,$B10)) this still returns an error, any thoughts? "Dave Peterson" wrote: You can replace your formula with =sumproduct(). =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99=A3)) Adjust the range to match--but you can't use the whole column. If you create the formula with the book1.xls workbook open, you may find it easier. Excel will adjust the formula when you close that workbook. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html shakey1181 wrote: is there anyway around this? [i am using =countif()] "Dave Peterson" wrote: There are some functions that don't work with closed workbooks: =indirect(), =sumif(), =countif() are a few shakey1181 wrote: I need the sheet to reference another file and update accordingly. I have entered the destination of the file required and cells to be referenced etc. This works fine if both files are open at the same time. However, when I open the main file, it asks me if I want to update, and I have to choose yes as it is referencing daily reports over a month, so I need it to update automatically. When I choose yes, the fields error and don't populate correctly, even though the destination in the formula is still correct. What am I doing wrong? Thanks in advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing chart sheets in vba | Charts and Charting in Excel | |||
Excel referencing across sheets | Excel Discussion (Misc queries) | |||
Copying formulas referencing multiple sheets | Excel Worksheet Functions | |||
Formulas referencing other sheets | Excel Discussion (Misc queries) | |||
Referencing Sheets in a Formula | Excel Discussion (Misc queries) |