When I use a formula to set-up the time member, the XFGETCELL formula that refers to this time member is stuck in #REFRESH. The weird thing is, if I go in the XFGETCELL formula and hit enter without changing anything, when I refresh the sheet it works and gets a value, but next time I close excel, re-open, re-connect and refresh it's stuck once again in #REFRESH. If I hardcode the time I don't have this issue, or if I concatenate to a hardcoded number, it also works.
As an example I have a list of 12 rows with numbers across 30 or so columns and a total at the end. The rows represents each month, so to get the last current month with numbers I use a formula (example countif) on the total column and then concatenate this to the year, so if I have data until October, I would get 10, so the formula returns 2021M10 for the current year and 2020M10 for the prior year.
Is this an issue with the add-in itself where it is confused on the order the excel formulas are being calculated?
We've tried playing around with that setting, when it is false and we open the file, the values are there, but the moment we click refresh, the value changes to #REFRESH and there does not seem to be a way to get it back except to go into the formula and hit enter. If the value is TRUE, it starts off as #REFRESH and does not change even after we click refresh, so it does not seem to be related to our issue.