Complex Formula was working, now failing

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
"Literally within 1 min of me posting my plea for help, I figured it out!!!"

Nice when that happens ;)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top