• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula calculation issue - index/match

blcexcel

blcexcel

Member
This is a very strange issue. I have an Index/Match formula that calculates incorrectly when I update the data source. But when I troubleshoot the formula by deleting part of the formula and then clicking undo to return the cell to the original formula the cell calculates correctly.

This is not an issue with the spreadsheet being on manual calculation.

I can not share the entire spreadsheet due to size and sharing of company data. But below is the formula that is causing trouble. Again the formula provides the correct answer after I mess with it, just not initially.

Very strange.

Any ideas on what the issue might be would be appreciated.

=IFERROR(INDEX(StoreData,MATCH($A488,StoreDataMerch,0),MATCH(S$1,StoreDataColumns,0)),0)
 
Do you have Calculation set to Manual?
Check in the Formula, Calculation Options Tab
 
The calculation is set to automatic.

I have the same formula for all the data columns with just a different reference for the column lookup. Most of the columns are calculating correctly. Some of the values in the columns that are an issue are calculating correctly.

I'm considering converting the formulas to vlookups. But I could have a similar issue using the vlookup formulas considering there isn't a reason I should be having this issue with the index/match formulas.
 
Can we see this file please? Most people would take the time to recreate the problem on a smaller scale if privacy is an issue. Upload something that fails so we can advise. It may help you see the problem if you try and recreate it anyways.

Take care

Smallman
 
Try the following
Close Excel
Open Excel
Open your file, no other files open
Ctrl+Alt+Shift+F9
Save your file
Close Excel
Open Excel
Open your file

How is it now ?
 
Hi ,

As a first step , I would suggest you remove the IFERROR part ; this is just masking the error , and makes debugging difficult.

Secondly , the MATCH statements are the problem , assuming you have all your named ranges defined correctly.

Lastly , when ever you come across such a problem , try troubleshooting it like this :

1. Identify the contents of the value being looked up ; in this case , they are the contents of cells A488 and S1. By identify I mean go to those cells , press F2 and F9 if the cells contain a formula , and note the contents ; many a time , the text string might have a leading or trailing space of space-like character.

2. Manually identify the location in the lookup range which matches this value being looked up ; let us say this location is cell J1237 ; now , in any unused cell , put in the formula =A488=J1237 , and see whether this returns TRUE or FALSE.

If you can do this and post the results , further debugging can be done.

Narayan
 
Hui, Everything calculates when I use Ctrl+Alt+Shift+F9. Do you know what this might mean?

I'm wondering if the issue is related to my data source. I'm using Oracle Smart View which updates the data directly in Excel. It seems Excel is not recognizing the dependent data has changed when I update the data.

Thoughts?

Thanks for your help!

Brian
 
Ctrl+Alt+Shift+F9 forces Excel to rebuild the dependancy trees that link the cells to each other. These can become corrupted or confused from time to time resulting in cells that don't get recalculated as they should.
It is important to save the file as soon as you have performed this so as to lock in the new Dependancy table
 
Hui,

Ctrl+Alt+Shifht+F9 did recalculate all formulas but it didn't permanently fix the problem. If I update the data the formulas in the issue columns still doesn't update. So I started to look at backup files to see if the problem had always been there. What I found is the problem started when I moved the columns to make some format changes on the report.

I recently started using index/match formulas instead of vlookups because everyone talks about the benefits. They are causing me a lot of grief at the moment. Have you heard of there being issues with moving index/match formulas? Or did I just get really unlucky and something really strange happened here?

Brian
 
I attached a small portion of the file. I had to take a lot out to get the size down to upload it.
 

Attachments

  • Store Review Report_send.xlsm
    808.5 KB · Views: 5
Which cells are misbehaving? , I assume it is E8:O16
How to replicate your problem as it all looks fine to me ?
 
The problem can't be recreated with the reduced file.

The formulas are the same except for the column reference so I copied the formulas from a "good" column to the "bad" columns and it took care of the problem.

Thanks for your help. You led me to the solution.
 
You must log in or register to reply here.
Back
Top