Guides

@Draaek

Ahh, that explains it.

The issue there is that you've got more of your swipp component items marked for saving than you currently own, and when it goes to calculate how many of that component you have available to put toward the trade it's coming up with a negative number rather than zero.

(You're not actually supposed to put the number of items you want to trade to swipp in the 'save' column; it will actually calculate those itself once it knows what trades you want to make. This is still a bug, though; someone might want to save a bunch of duskrats because they like duskrats, and it shouldn't bug out on them, either.)

Anyway, it's going to take me a minute to work up the fix, but I'm pretty sure this'll do it. :)

@arwentyan @Draaek Okay, bug squished! Let me know if there's anything still wonky after this. The main sheet is patched, and to fix the sheet you already have, you'll need to paste this into cell H2 of the Swipp Trades sheet (first cell under # Owned) and then copy it from there to the other cells in that column. (You can paste to multiple cells at once by selecting all of them and pressing ctrl-v.) [code]=if(not(isblank(B2)),sum(iferror(filter(ArrayFormula(if('Hoard: Food'!$T$2:$T - 'Hoard: Food'!$E$2:$E>0,'Hoard: Food'!$T$2:$T - 'Hoard: Food'!$E$2:$E,0)),'Hoard: Food'!$A$2:$A=B2),0),iferror(filter(ArrayFormula(if('Hoard: Materials'!$R$2:$R - 'Hoard: Materials'!$D$2:$D>0,'Hoard: Materials'!$R$2:$R - 'Hoard: Materials'!$D$2:$D,0)),'Hoard: Materials'!$A$2:$A=B2), 0), iferror(filter(ArrayFormula(if('Hoard: Apparel'!$S$2:$S - 'Hoard: Apparel'!$H$2:$H>0,'Hoard: Apparel'!$S$2:$S - 'Hoard: Apparel'!$H$2:$H,0)),'Hoard: Apparel'!$A$2:$A=B2),0), iferror(filter(ArrayFormula(if('Hoard: Familiars'!$S$2:$S - 'Hoard: Familiars'!$D$2:$D>0,'Hoard: Familiars'!$S$2:$S - 'Hoard: Familiars'!$D$2:$D,0)),'Hoard: Familiars'!$A$2:$A=B2),0), iferror(filter(ArrayFormula(if('Hoard: Battle'!$R$2:$R - 'Hoard: Battle'!$E$2:$E>0,'Hoard: Battle'!$R$2:$R - 'Hoard: Battle'!$E$2:$E,0)),'Hoard: Battle'!$A$2:$A=B2),0), iferror(filter(ArrayFormula(if('Hoard: Skins'!$R$2:$R - 'Hoard: Skins'!$D$2:$D>0,'Hoard: Skins'!$R$2:$R - 'Hoard: Skins'!$D$2:$D,0)),'Hoard: Skins'!$A$2:$A=B2),0), iferror(filter(ArrayFormula(if('Hoard: Other'!$T$2:$T - 'Hoard: Other'!$E$2:$E>0,'Hoard: Other'!$T$2:$T - 'Hoard: Other'!$E$2:$E,0)),'Hoard: Other'!$A$2:$A=B2),0)), "")[/code]

@AdeleneDawner
Alright it all works now, just a little tip for those who are fixing this manually: do select every cell starting at H2, if you paste the code on each cell individually you will have to change all the code B2 to the number corresponding to your item. If you do not do so it will register the item at B2 instead of the correct one.

EDIT: forgot about this, how exactly does the wishlist part work? So far there have been no changes upon those fields except for the fact that in the part of treasure until next set it is marking me -33,513. I'm assuming the change is influenced by the total amount of treasure that I set in treasure reserve but other than that I have no idea.

@Draaek That's probably that you don't have any AH values for the items you want - the finances page ignores anything without an AH value listed when it's trying to figure out what you can afford, and it's not very smart about handling it when that leads to an empty list. [s](Making it smart wouldn't be especially hard, but it would slow the sheet down even more, and I was starting to get annoyed with the speed issues already.)[/s] See below. I also just got done looking at arwentyan's sheet, and found another bug there. I've already updated the main sheet to fix it, but this one is on a hidden tab - you'll need to go to View > Hidden Sheets (10) > Swipp Calc, paste the following into cell B2, and copy it from B2 to the other cells in column B, just like with the last fix. (In other words, once again [u][i]don't[/i][/u] copy the formula into each cell individually.) Once you're done you can hide the tab again via the down-arrow next to the tab's name. [code]=if(not(isblank(A2)),sum(filter('Swipp Trades'!$G$2:$G,'Swipp Trades'!$B$2:$B=A2)),"")[/code] Thought about the wishlist section some more, and realized that there's a way to handle that that won't cause any extra lag. Paste this in starting at row 21, where it says "Most expensive buyable wishlist item:" [code]=if('Wanted Things Calc'!$F$2="","Wishlist not found - have you entered AH prices for your wanted items?",concatenate("Most expensive buyable wishlist item: ",iferror(filter('Wanted Things Calc'!$F$2:$F, row('Wanted Things Calc'!$G$2:$G)=max(filter(row('Wanted Things Calc'!$G$2:$G),not(isblank('Wanted Things Calc'!$G$2:$G)),'Wanted Things Calc'!$G$2:$GSettings!$B$7,$B$1-Settings!$B$7,0)))),""))) =if('Wanted Things Calc'!$F$2="","",concatenate("Next item: ",iferror(filter('Wanted Things Calc'!$F$2:$F, row('Wanted Things Calc'!$G$2:$G)=min(filter(row('Wanted Things Calc'!$G$2:$G),not(isblank('Wanted Things Calc'!$G$2:$G)),'Wanted Things Calc'!$G$2:$G>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0)))),""))) =if('Wanted Things Calc'!$F$2="","",concatenate("Treasure until next item: ",text(iferror(filter('Wanted Things Calc'!$G$2:$G, row('Wanted Things Calc'!$G$2:$G)=min(filter(row('Wanted Things Calc'!$G$2:$G),not(isblank('Wanted Things Calc'!$G$2:$G)),'Wanted Things Calc'!$G$2:$G>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0))))-if(and($B$1>Settings!$B$7,$B$1-Settings!$B$7>filter('Wanted Things Calc'!$G$2:$G, row('Wanted Things Calc'!$G$2:$G)=min(filter(row('Wanted Things Calc'!$G$2:$G),not(isblank('Wanted Things Calc'!$G$2:$G)),'Wanted Things Calc'!$G$2:$G>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0))))),$B$1-Settings!$B$7,0),""),"#,###"))) =if('Wanted Things Calc'!$F$2="","",if('Wanted Things Calc'!$K$2="","Item set wishlist not found - have you entered AH prices for your wanted items?",concatenate("Most expensive buyable wishlist set: ", iferror(filter('Wanted Things Calc'!$K$2:$K, row('Wanted Things Calc'!$N$2:$N)=max(filter(row('Wanted Things Calc'!$N$2:$N),not(isblank('Wanted Things Calc'!$N$2:$N)),'Wanted Things Calc'!$N$2:$NSettings!$B$7,$B$1-Settings!$B$7,0)))),""), " x ", iferror(filter('Wanted Things Calc'!$M$2:$M, row('Wanted Things Calc'!$N$2:$N)=max(filter(row('Wanted Things Calc'!$N$2:$N),not(isblank('Wanted Things Calc'!$N$2:$N)),'Wanted Things Calc'!$N$2:$NSettings!$B$7,$B$1-Settings!$B$7,0)))),"")))) =if('Wanted Things Calc'!$K$2="","",concatenate("Next set: ", iferror(filter('Wanted Things Calc'!$K$2:$K, row('Wanted Things Calc'!$N$2:$N)=min(filter(row('Wanted Things Calc'!$N$2:$N),not(isblank('Wanted Things Calc'!$N$2:$N)),'Wanted Things Calc'!$N$2:$N>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0)))),""), " x ",iferror(filter('Wanted Things Calc'!$M$2:$M, row('Wanted Things Calc'!$N$2:$N)=min(filter(row('Wanted Things Calc'!$N$2:$N),not(isblank('Wanted Things Calc'!$N$2:$N)),'Wanted Things Calc'!$N$2:$N>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0)))),""))) =if('Wanted Things Calc'!$K$2="","",concatenate("Treasure until next set: ",text(iferror(filter('Wanted Things Calc'!$N$2:$N, row('Wanted Things Calc'!$N$2:$N)=min(filter(row('Wanted Things Calc'!$N$2:$N),not(isblank('Wanted Things Calc'!$N$2:$N)),'Wanted Things Calc'!$N$2:$N>if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0))))-if($B$1>Settings!$B$7,$B$1-Settings!$B$7,0),""),"#,###"))) =if('Wanted Things Calc'!$F$2="","",concatenate("Total cost of wishlist: ",text(sum('Wanted Things Calc'!$I$2:$I),"#,###")))[/code]

@AdeleneDawner
Tested the new code but at the moment it gets pasted it gives me #ERROR! parse error, this only happens if there are no numbers entered in the AH price of any item I want.

If I do enter a number for AH price it gives me the previous error as well as a #N/A no valid data

@Draaek

...okay, pasting it in from notepad preserves the line breaks that tell it to fill in several cells, but pasting from here doesn't? That's weird.

*attempts to find a workaround*

@AdeleneDawner
Actually, the previous code you posted selected all the cells to fill just like the new code but the sheet keeps giving me the exact same errors. I'll share mine with you see if that helps with all the code mess.

@Draaek I'm betting you saw the code that was in a quote box rather than a code box - that still has a forum-related error, just a different one; the forum strips out a vital less-than sign.

The code in this pastebin works, though, if you copy it from the 'raw paste data' section rather than the main one - the main one also adds unwanted formatting, in the form of four or five spaces before each line. Why is sharing text so hard??

@AdeleneDawner
Alright this is my result with the code in the pastebin
]http://i41.tinypic.com/33bir60.png
Should it be like this or are we getting closer?

@Draaek I'm tired and making stupid mistakes. Pretty sure this one actually works though. And either way I'm giving up for now. :P