ref error

in the attached spread sheet data is pulled in from a folder containing individual spread sheets by selecing from the drop down list. it works very well. when i changed the formula in column o from =IF(AND(E6<=K7),-1,IF(AND(D6>=J7),1,0)) to =IF(AND(E7<=K8),-1,IF(AND(D7>=Jk),1,0)) i get a ref error each time i update the spreadsheet with new data. can any one solve this. funny thing is when i change the formula to =IF(AND(E7<=K7),-1,IF(AND(D7>=J7),1,0)) it works fine .hope you can help

Originally Posted by censura

in the attached spread sheet data is pulled in from a folder containing individual spread sheets by selecing from the drop down list. it works very well. when i changed the formula in column o from =IF(AND(E6<=K7),-1,IF(AND(D6>=J7),1,0)) to =IF(AND(E7<=K8),-1,IF(AND(D7>=Jk),1,0)) i get a ref error each time i update the spreadsheet with new data. can any one solve this. funny thing is when i change the formula to =IF(AND(E7<=K7),-1,IF(AND(D7>=J7),1,0)) it works fine .hope you can help

Hi,

=IF(AND(E7<=K8),-1,IF(AND(D7>=Jk),1,0))

should be

=IF(E7<=K8,-1,IF(D7>=J7,1,0))
or
=IF(E7<=K8,-1,IF(D7>=K7,1,0))


hth
---

Originally Posted by censura

i have changed the formula, thank you it does not solve the ref error though
thank you for your help
kind regards

Hi,

I was unable to duplicate your 'Ref' error, however the formula originally supplied was incorrect.
I also need to kill your macro on opening the book.

How is the Ref error currently being produced?

What is the formula, copied from the sheet, that is giving the error?

---

Originally Posted by censura

the ref error is being produced when i change the formula in column o from changing the e6 to e7 and the k7 to k8 and similarly changing d6 to d7 and j7 to j8 even when i use the correct formula you propossed.
hope you can help

I am still unable to reporoduce your stated error, the file you posted does not show any errors.
The change you suggest would occur normally on a formula fill

the formula

=IF(AND(E6<K7),-1,IF(AND(D6>J7),1,0))

could be reduced to

=IF(E6<K7,-1,IF(D6>J7,1,0))

however, either should work.

As told before, I need to cancel your macro which errors at the line

Set objFiles = objFSO.GetFolder(myPath).Files

so if something in the macro causes the error I would not be aware of it.