Guides

if we have the old hibden list, is there any way to just....... add in the new tasks? or, will i have to manually move over my data?

@Celestriakle I'm hoping to be able to simply add the new data to the end of the list so you should be able to just insert it in. I'll write up instructions when it's done :)

thank you!

@Goldia I'd love to be added to the pinglist for this! Thanks for the great tool c: (er, adaptation of a tool? Regardless, your work is appreciated!)

@Klawaw

thanks!! Will add you now :)

I'm currently part way through adding all the new quests in. Turns out it's quite a job, as I can't just add them to the end, so they'll mess up the order. Place, a whole new venue!! Will be letting peeps know when it's done!

@Tynethyne, @Karlia, @Klawaw, @Pachirisu, @Celestriakle

Changes done:

25 new Den slot Quests, requiring 53 new rows in the Main tab. To update, I would:

• Insert 53 rows above the last item showing now (that way any formulas auto update with the new range (they won't if you just add more data to the end).
• Also add another column in your sheet after the Quest ID number. This is so that there is an Old Id's and a New Id's column, for matching (IDA and IDB)
• Make sure your sheet is unfiltered and sorted by Quest ID number.
• Check the Master sheet is ordered by IDA, so that the new tasks are at the end.
• Copy the 53 rows for the new Quests and Paste -> Values Only into the empty rows of your sheet.
• Copy columns H, I and J and Paste -> Values Only into the equivalent columns of your sheet. I'm including the Quest Names here, because some of them have been tweaked slightly.
• This should mean that you have all the new quests, without losing the data you already had. You can now sort by IDB and delete the IDA column. Do not delete the IDA Column. Copy the IDB column, then Paste -> Values Only into the IDA column, then delete the IDB column. This will preserve the formulas.

Next

In the Coliseum tab there are 23 new rows and 1 new Venue column. To be honest, I would just copy the whole tab across from the Mater copy to yours. The only formula that may get affected is the one in the Needed column. which refers back to the Main tab:
=IF(VLOOKUP(A2, Main!C:M,10,FALSE)>=1,0,1)
That 10 will be a 9 if you've already deleted your IDA column.

There are no new Gathering items, so no updates req'd in the rest of the tabs, thankfully!!

I haven't gone through this with a fine tooth comb to check everything, and FR do tweak things occasionally, so there may be some bits that aren't as they used to be - please let me know if you find any errors!! Ditto if you have any problems with your updates, I'm happy to help out if needed..


I will leave the version of the Master sheet with both IDA and IDB columns up until a bit after rollover on the 14th March. After that I will remove IDA and revert IDB back to just ID, for any new people. If you see this before then but won't be able to update it, let me know and I'll get you a copy of them both.

Thanks Pachirisu for alerting me to the new Quests being available!

@Goldia thank you so so much! this is an amazing help. it all transitioned near perfectly, except one issue: for some reason, all the quests that i have everything for that are in the new row set are showing up neon green? which isnt a big deal honestly but i thought u might wanna know [img]https://cdn.discordapp.com/attachments/286861871087353856/818225316778344468/unknown.png[/img] EDIT: alright more issues, when i deleted IDA, all the green highlighting vanished? also i pasted in the new coli tab and its way off (like i need so many arena things, but it's saying none), but im not sure how to fix the formula bc ive never done spreadsheet formulas before ;;;;;; sorry for all the questions. also fsr my foraging tab got messed up too???? (i still need jungle lilies, but it's not showing up as needed. all the other gathering tabs seem to be fine, i need things from there too and those are showing up as needed.) EDIT (again): i tried just making a new copy of the sheet and pasting in my K and L columns but that did not fix either the coli issues or the foraging one or the highlight one x.x EDIT3: xD okay so deleting IDA fixed all the issues with the tabs. so the only issue that ACTUALLY needs fixing is the one of highlighting

@Celestriakle

Thank you! And thanks for highlighting this highlighting issue (*giggle*).

The joys of trying to update a sheet you didn't create in the first place, and haven't looked at in over a year!! It turns out that there's a hidden column that the conditional formatting refers to.

If you look at the column headers, you'll see that Column M (or L, depending on whether you've already deleted IDA) has a little arrow at the end of it. Click on that and the hidden column Available appears. You'll see that it's filled with a formula that returns either TRUE or FALSE. This formula didn't copy into the new cells. You can drag the formula down to fill it into the empty cells and that should make the highlighting work as it should.

You'll note that column N doesn't have the little arrows indicating a filter. You can press the filter button twice and it will re-establish the filter so that it includes the Available column, which means it should be included next time there's an update. You can then rehide the Available column.

I've updated the Master copy with the above fix too.

Let me know if that doesn't sort your highlighting issues or you need clearer instructions :)

@Tynethyne, @Karlia, @Klawaw, @Pachirisu pinging you in case you're having the same problem!

Edit - sorry, more steps to take - see below.

OMG this is more complicated than I thought!!!

Anyone who hasn't already deleted their IDA column - don't!! Copy your IDB column and then Paste -> Values only to overwrite your IDA column. This will preserve all your formulas.

If you've already done that, there's a few formulas that will need updating - sorry!!

That hidden Available column on the Main tab should have: =COUNTIF(Available!$B$1:$B$10, H2)>0 in row 2, then drag the formula down the table.

Click on any cell in the Quest Name column. Click on Format -> Conditional Formatting. A pop up will appear on the right. Click on the bright green option. In the field below 'Custom Formula is:, enter =AND(AVERAGEIF($H:$H, $H2, $L:$L)>=1, $M2). Click Done

Repeat for the mid green option, using the formula =AVERAGEIF($H:$H, $H2, $M:$M)>=1

There's also a hidden tab called Available. Unhide that using the four little horizontal lines in the bottom left of the sheet. The formula in column B should be: =SMALL(UNIQUE(FILTER(Main!$H:$H, Main!$J:$J=FALSE)),A1) in row 1. Drag the formula down. Rehide the tab.

And I think that gets it all sorted!!! I am so sorry for the extra confusion!!

Woah, this is so helpful, I will definitely be using this! :D