Formula Fails to Update when referenced values are entered

modorific

New Member
Joined
Sep 24, 2009
Messages
4
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!
:confused:
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

New Member
Joined
Sep 24, 2009
Messages
4
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

New Member
Joined
Sep 24, 2009
Messages
4
Also, it didn't come through in the original paste, but this is an array if that makes a difference.
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

Well-known Member
Joined
Sep 26, 2006
Messages
705
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

New Member
Joined
Sep 24, 2009
Messages
4
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

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,939
Messages
5,447,410
Members
405,451
Latest member
BalbasNiBarabas

This Week's Hot Topics

Top