Sheet will not alter a sort unless I repeatedly press F2 and <enter>

[rcmii]

This has to be a simple solution that I'm missing.

I have two tabs. Tab 1 reads Tab 2.
I have sorted data in Tab 2 and Tab 1 reads it.
I changed the sort order on Tab 2, but Tab 1 still shows the old sort.

This is a general formula and not a macro.

Automatic calculations are on (although I realize it's not a calc, per se)

The only way to get Tab 1 to reflect the new sort on Tab 2 is to go into each cell (there are approx. 800 rows and 32 columns) and repeatedly press F2 and then <Enter>. Yes, I can survive a few cells of this. But to have to manually do this to more than 25,000 cells is unworkable.

Any ideas on how to fix this? I have no idea what is going on. I had this issue about 20 years ago but for about 30 rows, I was manually hitting F2+ Enter to solve quickly. Changing format doesn't seem to work and each sheet is set to the same format for each sheet entirely.

What's going on? Any workarounds?

Thanks in advance for any guidance.

 

[Joe4]

Can you explain the purpose/structure of sheet two?
If we understand exactly what it is used for and how it is supposed to work, we may be able to provide solutions or alternatives.

 

[rcmii]

In this simple example, I was simply re-sorting source data. This was because I was using OFFSET/MATCH formulas to look at the source data, and add each row of specific data until no more data in the MATCH reference. It was a formula set I created years ago and moving it to a new dataset.

I cannot seem to get the XL2BB to work so my sincerest apologies for typing vs attaching something.

First cell created the starting point
=IFERROR(@OFFSET('TB Data Conversion'!$M$1,MATCH($F133,'TB Data Conversion'!$M:$M,0)-1,1,,),"")

Second cell created the second row/cell
=IFERROR(@IF(@OFFSET('TB Data Conversion'!$M$1,MATCH($F134,'TB Data Conversion'!$M:$M,0),,,)<>$F134,"",OFFSET('TB Data Conversion'!$M$1,MATCH($F134,'TB Data Conversion'!$M:$M,0),1,,)),"")

Third cell created the remaining pieces until no further data was found that matched the MATCH as I copied down the formula.
=IFERROR(@IF(@OFFSET('TB Data Conversion'!$M$1,MATCH($F135,'TB Data Conversion'!$M:$M,0)+ROW($A$1),,,)<>$F135,"",OFFSET('TB Data Conversion'!$M$1,MATCH($F135,'TB Data Conversion'!$M:$M,0)+ROW($A$1),1,,)),"")

(honestly, not sure why I did it this way (this being the first three row cells being different, years ago, but it worked and I move forward because I didn't have time to figure it out as I was under a deadline)

Fourth cell and beyond simply modified the Third cell the the +ROW($A$1) pieces that became $A$2, $A$3, etc. stopping at $A$20 (I added the $ pieces because of a worksheet issue that I had where even though I would think that as it copied down and added one row, it wasn't working. So I did the fastest solution to lock it to cells as it counted down about 20 rows and then started a new search with a new row with specific data.

So really, what it came down to is that my rows sometimes returned data out of order and sometimes it did not. I figured out it was a sorting issue as I realized every item I was looking for in each cell had to be in order vs just inside the dataset at any spot in the column.

When I re-sorted on the source tab, the sort worked fine. But data was not changing on the tab that the OFFSET/MATCH formulas looked at.

That's when I did the F2+ENTER that got me to this question. Again, I have had the F2+ENTER issue in other small reports for years, but the data usually wasn't a huge volume and I could hit F2+ENTER for 30-40 cells and get it over with. Now with over 25,000 cells, it is obviously not practical.

This is a lot of typing and while I'm hoping it makes sense on your side, I can understand if it does not given that you're not the author of the file! If I can ever get the XL2BB add-in to work (none of the searches at this site have worked....could be user error), I'll attach my file.

Attaching a snapshot of the data.
Material is the "Parent"
cells beneath are the formulas above, row 1 is "first Cell" above, row 2 is "Second cell" above, and so on. until it gets to the new parent at Labor. If there were only 3 accounts, then only 3 would show up and not the 12 that show up. At labor, it's actually 1 row that shows up, but that could change in future months as data changes.