Array Formula Problem

[mcmahobt]

I'm creating a forecasting worksheet that pulls values from a separate sheet using an array. Starting out, the array was working fine. I was able to copy, paste, and drag it through the necessary cells easily; that is, until it suddenly stopped working.

About halfway through my forecasting sheet the array formula no longer pulls any data. I double checked to make sure the data sheet from which it is pulling from was referenced correctly (which it was), but still to no avail.

A quick google search directed me to make sure things like Automatic Calculations was turned on (which it was), and to F9 the cell to force a calculation, but still nothing.

I could post the formula if anyone thinks it would be helpful, but it is pretty long-winded and I'm not sure how much it would help. I'm essentially trying to see if anyone has run into this type of problem before and whether there is a solution I am missing, or if this may just be a bug with EXCEL 2010 arrays, as there have been in the past.

Any help would be very helpful!

 

[Andrew Poulsom]

It's impossible to diagnose your problem without seeing the formula and what it returns when it "no longer pulls any data".

 

[Asala42]

Can you tell us what it's doing exactly? Showing a #VALUE or #N/A error? Zero value when you expect it to be a number? Or does it just look like it's not recalculating?

If it's not calculating, as hinted by your web searches, it's possible you have a circular reference at work. Check your bottom bar for the word 'circular'. If you see that then you probably have a bad formula somewhere - usually it comes down to a single formula referencing the wrong range. You'd need to resolve that before you can really trust any formula result in your file.

If it's an error, it's possible you just have an error in the cells your formula is referencing. These errors often just get passed up through formulas referring to them. Clearing them from the data will solve the problem, as can redesigning the formula to account for them.

If it's none of these, please be more specific. Post the formula, the results you expected, and what it's spitting out instead.

 

[Trouttrap2]

Besides what has already been said, you might try and use the formula evaluator to find where the problem is. Click
ALT, T, U, F and step through the formula.

 

[mcmahobt]

Thank you all for your help.

Trouttrap, it ended up being your advice that helped me solve my problem - there was a hidden row within my raw data which my array was pulling from that was throwing everything off. The formula evaluator helped alleviate that.