Guides

@ladra

Thank you so much!! This tracker has been immensely useful (just used it while planning two outfits yesterday), thanks for all your effort in keeping up with new apparel :D

@ladra

I'm a little confused (and that might entirely be due to 2 hours of sleep and not anything that I'm looking at). I was inputting everything last night and decided okay, I want to try and complete some sets. So I looked at my sets tab to see which ones would be easy to complete. Only one of them wasn't right?

Specifically, I have all 4 of the Violet flower pieces. And now that I'm thinking about it, I think it has to do with the name of the items, but I digress. When I first had it sorted non-alphabetically, the sets page said I had 0 pieces of the Violet set. When I resorted to be alphabetical, it now says I have 2 pieces of the set, even though all 4 are marked as had.

Now, for the Marigold set, the code is:

=ARRAYFORMULA(countif('Apparel Tracker'!B2:B2900&'Apparel Tracker'!C2:C2900, "*Marigold*"&"Yes") )

Which makes sense to me. That says hey, if this item is marigold and you have it, count it here. Cool. That counts right, it works, no issues. But the code for the Violet set is:

=ARRAYFORMULA(countif('Apparel Tracker'!L2:L2900&'Apparel Tracker'!C2:C2900, "*VF*"&"Yes") )

Which does not make sense to me. As far as I can tell, there is no data in column L that it should be pulling from at all, because that column is empty? On top of that, there are 2 items in the Violet set that do not say Violet Flower. So I wonder if it's magically searching the right items by looking at column L (again, I don't understand that but it's early and I certainly don't understand everything) but then missing those particular items? I'm looking at the Violet Corsage and Violet Lei as culprits, in particular.

So! If you would be willing to take a look and see what you find, that would be great. If there really is supposed to be data in column L, it could b e I just foobared my own sheet, in which case that's fine, I'll fix it. Otherwise, I can try and help tweak the code around a little when I'm more awake if there is an issue.

Edit: Also, on the Sets page, the there are some formulas where the range for counting the total number of pieces is incorrect. White Birdskull doesn't count the total number of pieces in the set because the range is from cell 3 to cell 1817 (so cell K22 should be =countif('Apparel Tracker'!B2:B2900,"*white bird*") to fix), and the Greenskeeper count for how many pieces I actually have is also off because of showing that range (so cell C35 should be =ARRAYFORMULA(countif('Apparel Tracker'!D2:D2900&'Apparel Tracker'!C2:C2900, "*Greenskeeper*"&"Yes") ) to fix).

Thank you for reading my long rambling post, and I probably wouldn't have noticed all this except I entered every single piece of apparel I have in the sheet over the course of two hours last night so I kind of know when I eyeball the number that something is off. I promise I tried to make sense I sorry for bugging you love your tracker thanks again and can I be added to the pinglist please?

@ShadeOfChaos

No no no, you're not bugging me at all! Please don't worry. :)

You're absolutely right about it having to do with the names of things. Some items, like the marigold flower set, are very straightforward. But with the violet flower set, and many others, you also have items like the Violet Highnoon Hank and Violet Deepsea Bulb complicating matters. I suppose we could have made a formula that subtracted all the non-applicable items, but honestly that would have just created a lot more work.

What I did was make set codes for the offending items and place it in column L. But since I'm way too much of a perfectionist I made those set codes white so nobody could see them. I talked about them in every update for a while but then I figured ok, everyone has heard about them, I can stop now. That was my mistake, any I apologize. I really need to put a note on the first post and in the sheet itself for people like yourself who are just discovering the spreadsheet after the sets tab was implemented.

So if you resort everything in your sheet alphabetically it SHOULD fix that. Just be sure, when you're sorting in the future, to select the L column as well. If you still find that something is off try giving L black font and I can take a look and make sure everything has the correct code (you could always figure it out yourself by looking at the formulas as you have but I feel responsible for all this trouble).

As for the range, I've had to expand the amount of cells on the spreadsheet several times since the sets tab was created due to new releases, and probably forgot to change the formulas in response. Whoops. That will definitely be addressed in the next release but you could always change it yourself.

Very sorry for all the commotion and thanks for bringing all this to my attention! I will definitely add you to the pinglist. Go get some sleep. :)

@ladra

It must have been something I sorted, because the formula bar didn't even have anything in it when I looked, but I'll check again when I get home. It might be easier to just add the item names when it's only a few pieces, but I can understand why you did it how you did.

Also, you could change it so the reference was the whole column, and that way you never have to update it again. Unless sheets doesn't let you? I'll check it out when I'm off mobile and rested instead of at work and exhausted. :P

@ShadeOfChaos

I'm going to revisit it over the weekend and see if I can't just fix it so that the set codes aren't needed. At the time it was an overwhelming amount of work because ALL of the sets had to be created at once, so I jumped on any opportunity to make it a little bit easier. In hindsight I should have done it a little bit at a time, but you live and learn.

I can't promise it will get done today because I'm buried under a mountain of homework, but it'll be done sometime by the end of Monday.

@ladra
This is really cool and I can't wait to use it, but right now I don't know how to use it and if I'll mess everything up if I change it (which right now I can't, since it's on view only)

@LightningShatter

Save a copy to your drive. It's under File - Make a copy. Then you will have your own editable version.

@LightningShatter

Exactly what ShadeOfChaos said! If there are any features in particular that you're unsure about using feel free to post here or send me a PM and I can walk you through it. :)

@ladra

I fixed the Sets page. It will not reference column L at all, it keeps proper code, and it will search no matter how many new lines you add in there. I messaged you a link so you can see mine.

Also, you use a lot of wildcards, which makes searching fun. The neat thing is you don't have to say "*word*". For instance, you do not have to use "*navy*" for the navy wrap set. You can instead say "Navy*"&"*wrap*". That will bring up anything that starts with Navy and uses wrap anywhere in the item.

Just let me know if you don't understand what I did, and no pressure to change yours, I just figured I had time and would do it real quick. ^_^

Edit: Also, I went back and read the whole thread. If you want, I can also update my Sets sheet to be a different order and have the conditional formatting you wanted if that would make it easier. Also, if there are more of the single sets you want to add (like, say, a roundhorn set) just let me know and I can code those in too. I love working in Sheets, so drop a line and I'll get it working and let you know when.

I think I have just fallen in love <3 I had hoped to find something like this but I never dreamed of finding something so perfect! It's amazing. Thank you for the hard work. And please put me on the pinglist <3