Formula Fails to Update when referenced values are entered

[modorific]

I have a 2 sheet workbook, one of which contains a table with the following formula:

=IF(ISERROR(INDEX(Data!$A$1:$K$999,SMALL(IF(Data!$A$1:$K$999='Group P2 Grid'!$C$170,ROW(Data!$A$1:$K$999)),ROW(Data!$2:$999)),3)),"",INDEX(Data!$A$1:$K$999,SMALL(IF(Data!$A$1:$K$999='Group P2 Grid'!$C$170,ROW(Data!$A$1:$K$999)),ROW(Data!2:1003)),3))

Wherein if somebody enters a coded value (i.e., XH, XL, XM) into a column in the 'Data' sheet, the table should populate with the data in column 3 on the data sheet.

The formula was working previously, but it now no longer updates automatically. What do I need to do to get my table to populate with the results of the formula above? I am clearly very confused.

Thank you!

 

[shemayisroel]

Hi & Welcome,

Turn on auto calculation

Assuming excel 2003

Tools -> options -> calculation

Then ensure automatic is the option selected

Try also F9 as a one off calculation

 

[modorific]

I checked and my auto calc is already on (i'm using 07, where it's under the start button and excel options), i also F9ed and even used Shift+F9 to no avail. I should have written that in my first post. Sorry.

 

[modorific]

Also, it didn't come through in the original paste, but this is an array if that makes a difference.

 

[shemayisroel]

Also, it didn't come through in the original paste, but this is an array if that makes a difference.

Sure does

Are you confirming using Ctrl + Shift + Enter?

Not just enter...

 

[Claymationator]

The formula was working previously, but it now no longer updates automatically.

So, it was working previously but won't update automatically. Is it updating at all? You mention that you have checked for the automatic calculation, as well as F9...So you are saying that it doesn't update at all now?

 

[modorific]

it doesn't update at all now. All of a sudden, blanks everywhere. I enter into the cell to Ctrl+Shift+Enter, nothing. I've done a find and replace on the = key, replacing with =, nothing. totally bizarre. If i change the value it is referencing, nothing. Now, it shows that it is calculating, but the results never appear.

 

[shemayisroel]

it doesn't update at all now. All of a sudden, blanks everywhere. I enter into the cell to Ctrl+Shift+Enter, nothing. I've done a find and replace on the = key, replacing with =, nothing. totally bizarre. If i change the value it is referencing, nothing. Now, it shows that it is calculating, but the results never appear.

Hmm I'm out of ideas unless you could supply some more info to go with?