SUMIF Stops working when data is refreshed

[Jazzer]

Hi

I have the following sumif which returns the correct data. =SUMIF(DATA!L7:L43,test!D11,DATA!R7:R43)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The data on the "data" tab needs to be refreshed on a daily basis (this is done by macro which deletes and imports the new data from a text file) however when my data is refreshed I get #REF! and my formula becomes =SUMIF(DATA!#REF!,test!D11,DATA!#REF!)<o></o>
if I then manually change the formula back it still picks up the correct data.<o></o>
<o></o>
any idea how I can stop this please?<o></o>
<o></o>
Many Thanks<o></o>
James<o></o>

 

[RoryA]

I suspect your macro is deleting the cells rather than just clearing them. If you can't change that, then use something like:
=SUMIF(INDIRECT("DATA!L7:L43"),test!D11,INDIRECT("DATA!R7:R43"))

 

[Jazzer]

Thanks Rory.

much appreciated<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Jazzer]

Just trying to use the same principle on another sheet however I am receiving a #REF! Error<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Can you see any reason for this...<o></o>
<o></o>
=SUMIF(INDIRECT("LCH DATA!E10:E12"),LCH EUR!F6,INDIRECT("LCH DATA!F10:F12"))<o></o>
<o></o>
Many Thanks<o></o>
James<o></o>
<o></o>

 

[RoryA]

You have spaces in the sheet names so you need to enclose them all in single quotes:
=SUMIF(INDIRECT("'LCH DATA'!E10:E12"),'LCH EUR'!F6,INDIRECT("'LCH DATA'!F10:F12"))

 

[Useful]

Just trying to use the same principle on another sheet however I am receiving a #REF! Error<o></o>
Can you see any reason for this...<o></o>
<o></o>
=SUMIF(INDIRECT("LCH DATA!E10:E12"),LCH EUR!F6,INDIRECT("LCH DATA!F10:F12"))<o></o>
<o></o>
Many Thanks<o></o>
James<o></o>
<o></o>

Hi! i think this reason closing with your macro code (after deleting prosess it becomes error)
can you post your macro code?

 

[Jazzer]

You have spaces in the sheet names so you need to enclose them all in single quotes:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

=SUMIF(INDIRECT("'LCH DATA'!E10:E12"),'LCH EUR'!F6,INDIRECT("'LCH DATA'!F10:F12"))

<o></o>
<o></o>
Thanks Rory - this has eradicated the ref error<o></o>
however just one last thing to resolve<o></o>
<o></o>
the original sumif returns the correct number<o></o>
<o></o>
=SUMIF('LCH DATA'!E10:E12,'LCH EUR'!F6,'LCH DATA'!F10:F12)<o></o>
<o></o>
the new sumif is now returning TRUE<o></o>
<o></o>
=SUMIF(INDIRECT("'LCH DATA'!E10:E12"),'LCH EUR'!F6,INDIRECT("'LCH DATA'!F10:F12"))<o></o>
<o></o>
if I can fix this my sheet will be up and running.<o></o>
<o></o>
Thanks again<o></o>
<o></o>
James<o></o>

 

[RoryA]

I can't see any reason why that should be. What is the cell format?

 

[Jazzer]

Cells are on number format<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
It seems strange as the other sheet is working fine<o></o>
<o></o>
=SUMIF(INDIRECT("DATA!L7:L43"),test!D11,INDIRECT("DATA!R7:R43"))<o></o>
<o></o>
above returns the desired numerical answer<o></o>
<o></o>
=SUMIF(INDIRECT("'LCH DATA'!E10:E12"),'LCH EUR'!F6,INDIRECT("'LCH DATA'!F10:F12"))<o></o>
<o></o>
above returns TRUE<o></o>
<o> </o>
<o> </o>