• 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.

Stores by City and State - Changes in DATA are not reflected in RESULTS

Hello,

Attached is a spreadsheet with two worksheets: DATA holds the, well, data. RESULTS is where I have my array formula. The formula appeared to be working properly. However, when I tested it, by changing the cities listed in the DATA sheet - all to the same city - the formulas would not recalculate and change the results.

I've never run into this before, so I'm puzzled.
Thank you in advance!
 

Attachments

This is really helpful, but I did discover an anomaly that I simply can't figure out. I updated the spreadsheet and formulas to match my 'real' spreadsheet exactly. However, even though my MSTR_DATA only fills to Row 17, I have to enter Row 23 after SMALL(IF for Column I, but, I do not need to do this for Columns C or J.
=IFERROR(INDEX(MSTR_DATA!$C$2:$C$17,SMALL(IF(MSTR_DATA!$I$2:$I$23=$H$1,IF(MSTR_DATA!$J$2:$J$17=$H$2,ROW(G$2:G$23)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

So, strange.
 

Attachments

herofox

Active Member
my dear every Result in Column H is correct,I don't Know what the problem with You?!!
every thing is ok ,it's good array (Ctrl+shift+Enter) ,not as Normal only Enter
Please do not delay in reply
 

Attachments

You are correct, what you sent back does work. Thank you.

However, I'm puzzled at why the ranges(see in red) have to be larger than the actual range, in order for the formula to work. In the sample spreadsheet, my source list only has 17 rows, but the formula doesn't work unless you set the range for >17 (ex: $C$2:$C$170 vs $C$2:$C$17).

What I sent you: =IFERROR(INDEX(MSTR_DATA!$C$2:$C$17,SMALL(IF(MSTR_DATA!$I$2:$I$23=$H$1,IF(MSTR_DATA!$J$2:$J$17=$H$2,ROW(G$2:G$23)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

What you sent back:
=IFERROR(INDEX(MSTR_DATA!$C$2:$C$170,SMALL(IF(MSTR_DATA!$I$2:$I$170=$H$1,IF(MSTR_DATA!$J$2:$J$170=$H$2,ROW(G$2:G$170)-ROW(G$2)+1)),ROWS($H$3:H3))),"")

Anyways, I've got it to work, just mysterious on why the ranges reference has to be larger.

Thank you!
 

herofox

Active Member
Certainly this is not a measure
But I wanted the range to be larger so maybe you could increase the data in the future
 
Top