Complex Formula was working, now failing

[spydey]

I have a complex formula that was previously working for the last 6 months.

Now when I try to run it, it just gives me a #VALUE ! error.

The data set over the last 6 months and now is basically the same volume.

I haven't change the formula, the layout of the columns, tab names, nothing ....

I tried to evaluate the formula but excel freezes and fails.

About a week ago something happened with my Excel and it was like many of the settings were reset. Many of my macros that previously worked, now don't.

Previously, when I would open a workbook and press ALT+F8, I could run the selected macro.
Now, it shows the various macros, but the RUN button is not available.

I am wondering if the Excel reset caused something to happen which is affecting my formula.

The formula is the following:

=IF(OR(ISBLANK(A2),G2>H2),"",SUM(--(MMULT((ROW(INDIRECT(G2&":"&H2))>=TRANSPOSE(IF(Holds!$A$2:$A$4639=A2,IF(Holds!$I$2:$I$4639="YES",Holds!$K$2:$K$4639))))*(ROW(INDIRECT(G2&":"&H2))<=TRANSPOSE(IF(Holds!$A$2:$A$4639=A2,IF(Holds!$I$2:$I$4639="YES",Holds!$L$2:$L$4639)))),Holds!$K$2:$K$4639^0)>0))+J2)

Entered with CSE.

Previously when I would run it, it would take like 10 mins to run on several thousand rows of data.
The data was built as a table and so it would populate all cells in that column for all the rows in the table.

Now, when I run it and it gives me the error, it just fills that first cell in the first row.

I cannot for the life of me figure out why it is failing!! I have been using it for 6 months without any issues.

Any thoughts, ideas, pointers, etc., would be greatly appreciated.


Thanks to everyone!!

-Spydey

 

[spydey]

Ok, I just spend two days trying to figure out the issue.

I researched online, looked at examples, read many a post, etc.

Literally within 1 min of me posting my plea for help, I figured it out!!!

LOL

In my population in the Holds tab, I had a #VALUE ! error that was causing a feedback to the whole formula.

I got rid of it and now it works again!!!

I wish I had found it before taking the time to write up a request for help .... lol

Thanks to anyone and everyone who was going to help. I appreciate it!

-Spydey

 

[PaddyD]

"Literally within 1 min of me posting my plea for help, I figured it out!!!"

Nice when that happens