I am encountering an issue whereby results are not maintaining their row after sorting. I have tested this with both two functions for obtaining prices for both min sell and max buy (note that the below are only examples for min sell).
=MIN(EVEONLINE.MARKET_ORDERS().price)
and
=EVEONLINE.MARKET_ORDERS_STATS.sell.min
Initial table looks like this and is correct - this was set up when the TypeIDs were in order:
There is a bug when using the built in simple table sorting and referencing cells outside the current sheet. (is your region_id, location_id somewhere else).
Doing so will mess up the order when sorting so the columns aren’t pointing to the correct relative cell.
Not sure if the last user to encounter this found a fix for it, but I remember I manged to reproduce it and it was reproducable without the addin. So sadly there is nothing the addin can do, it’s more of an excel bug.
edit: the bug appears also when referencing a current sheet using Sheet1!A1 type of reference. When A1 works
And you are correct - my region_id and station_id values are on another sheet. I’ll hard-code them into the formula and test.
It also explains why I am not encountering the same issue on the other sheet - it’s because they are referencing the item_id of the row within the same sheet.
I’ve got a small update on the bug report, since I just got word from Microsoft yesterday.
This issue is quite old, and appears in Excel 2021 and thus it’s a potentially breaking change that might mess up existing sheets. So we won’t be getting any patch to fix the behaviour.
Hopefully you’ll be able to work around it!
I must have missed your edit and so when I hard-coded the id values the error was still present.
As your message was about sheet names - and my formula had the current sheet name for the item_id part - I thought “perhaps it breaks with all sheet names”.
Sure enough, after removing the sheet name and just leaving the cell reference it worked!
I came back to announce my findings but this time saw your edit lol
I have another question though - each time i open the document, it doesn’t automatically refresh the Eve formulas. Things like item prices, system manufacturing/reaction indexes etc. Do you know a way of doing that? Currently I re-sort columns and it those formulas get re-initiated but wondering if there’s another way.
For refreshing formulas there is a shortcut to recalculate all cells, CTRL + ALT + F9.
F9 and different alt keys for more specific refreshing (I only got the refresh all version to work).
It used to refresh on startup but for performance reason they turned that off. It was crushing for large large sheets to fire all the requests off at once, so it makes sense. Kinda wish the addin sidebar had a button that would refresh all cells.
Some people use control cells to update only certain parts, but that’s done by IF wrapping around the formulas based on a true/false dropdown list cell. Nothing overly complicated but helps if for example you just wanna do price updates, but not fetch static looking data, or recalculate something that’s cached for 1 hour anyways.