Results 1 to 9 of 9
-
2017-02-18, 07:49 AM (ISO 8601)
- Join Date
- Jan 2007
- Location
- Derby, UK
- Gender
Help with indexing spell lists [Word/Excel/????]
Okay. While I have a short period of time before Panto kicks off again this afternoon, I need some help from the tech-savvy denizens of the playground.
I have reached a stage in my massive reorganising-the-spells-project where the end is in sight and I need to look at the next step.
Currently, I have (three sets of) big huge lists for each character class with their spells and page references from the original sources, thus:
Spoiler[/img]
As I have now moved the majority of, well, basically everything that is not in the PHB, PHBII and Spell Compendium into word documents (with revision errata and homebrew-specifica modifications) - principally so I don't have to take so much guff with me down to the club on a Monday night, the old sources are now out of date. My players wpuld like updated pages references to the new sources, this spells document.
As part of this revision (which included coping up/fixing the spells from Relics & Rituals as my copy is disintegrating and compiling two other seperate spell documents together), I have made a table of contents which populates from the spell headers thusingly:
Spoiler
(Both things are in word, in case that wasn't obvious.)
What I need help with is to now determine how, through the use of the tools available in MS Office 2010, how I turn said table of contents into some sort of Thing that I can then paste into the appropriately sorted spells lists and replace the page references. I would like, as you can imagine, to automate this process to a degree, so that when/if I add a new spell the very second the job is done, I don't have to do it manually every time.
At the moment, I am envisioning something like:
1) Go to (say) Cleric Spell list.
2) Sort 1st level cleric spells by source column.
3) Paste table into Excel. Delete all sources that do not requires new references (e.g. PHB/PHB2/SpC - some manual adjustments for revisied spells from those sources will be required, but there's no way around that). Clear remaining references.
4) Add number of blank rows equal to spells remaining from aforementioned three sources to word document list, so that when I paste things back in, I can just delete duplicates.
5) Sort table in excel alphabetically.
6) Use previously created [formula] wot looks at spell column (using find text string between start and ":") and looks up (LOOKUP) on data table on next sheet. Data table is extracted from table of contents, with output column somthing like =&MstrRuls p&"C27".
7) Populate blank references column with values derived from above.
8) Copy back into word document. Delete duplicates. Sort table back to alphabetical order.
9) Repeat with level 2 through 9 spells (and then for all other classes (twice; the master set of lists won't be altered, since that is not used by the players or me, just holds all of the orignal source data, including any spells we've deleted.))
At the moment, the "turn table of contents into data " I'm not sure about; I know enough spreadsheet mechanics to be able to extract the spell names to know how to compare strings. (I might be able to figure out how to do it, but given the hours already sunk in, it's quicker to ask for help and then I can be getting on with the job!)
I would LIKE, if possible, to be able to somehow makes the colours of the cells populate (and also paste into Word), but I have no idea how to do that or if it is even possible. If not, I'll just have to do it manually.
(The colours have a purpose. Blue is new spell or significant errata, grey is a campaign specific and yellow is what, if the campaign-specific calls it something different, what the generic name is.)
If there is an even better/more efficient way to do this, I'm open to suggestion; but given my limited experince with Excel (and none whatsoever with Access o somesuc), you will have to explain to me like I'm five.
-
2017-02-18, 08:15 AM (ISO 8601)
- Join Date
- Aug 2013
Re: Help with indexing spell lists [Word/Excel/????]
I should probably be able to do this. But it's been a while since I mucked about for reals in Word and Excel.
Regarding Indexing. If you properly make each spell's name a heading Word can just by refreshing index update it with references (ie internal links) and all. Maybe you did that already, kinda looks like.
Yes you can colour the background of cells depending on content, if you ahve some ruels that cna be logically followed. There's Conditional Formatting, or if it's very complicated or you want to colour on something formating can't recognize, can do it with a macro in Excel.
The pasting back and forth is gonna screw up at some point am sure, even though Office products supposedly seemlessly copy/paste back and forth (anything other than raw data will tend to be messed up, esp formuals will give you trouble and formatting). You would probalby be better off doing it all in Excel as essentially you have a rudimentary database of sorts going there.
If you would split up the information into several fields in Excel you'd get more power to handle it. Say: Spell Name. Description. Source. Page. Then you can sort it by source eg witohut extra shenanigans and the conditional formating might be easier to work.
As you are doing a lot of work of data kind I think you will best be served by moving it all to Excel.
-
2017-02-18, 11:22 AM (ISO 8601)
- Join Date
- Nov 2006
- Location
- England. Ish.
- Gender
Re: Help with indexing spell lists [Word/Excel/????]
Well, the simple bit is the "turn table of contents into data" bit. If you copy the table of contents you can paste it directly into Excel - you need to use the "Paste Special" option, and select "Text". This will drop the titles and page numbers into two seperate columns.
This seems to work OK in Word/Excel 2007 (I've just done it...)
The more complicated stuff... Well, as Snowblizz notes, swapping data between Word and Excel on a continuing basis is going to be something of a nightmare. I would suggest you hold the lookup stuff in Excel, and what writeups there are in Word and just swap the TOC between Excel and Word. (Anything that involves sorting is best done in Excel...)
If I can have a look at the documents concerned (or a small subset to do one trial) I might be able to come up with something better. I might also be able to put together a couple of Excel VBA macros to speed things up.Warning: This posting may contain wit, wisdom, pathos, irony, satire, sarcasm and puns. And traces of nut.
"The main skill of a good ruler seems to be not preventing the conflagrations but rather keeping them contained enough they rate more as campfires." Rogar Demonblud
"Hold on just a d*** second. UK has spam callers that try to get you to buy conservatories?!? Even y'alls spammers are higher class than ours!" Peelee
-
2017-02-20, 11:44 PM (ISO 8601)
- Join Date
- Jun 2009
- Location
- The One in the Middle
Re: Help with indexing spell lists [Word/Excel/????]
I've done much the same thing using Microsoft Access. I created a page that allows me to input all of the relevant information on a spell, and once a spell's in the data base I can search it by name, by class, by level, by school, etc. It was a pain to do it initially, and I'm not enough of an Access guru to tell you an easy way to convert a Word or Excel file cleanly, but it has definitely been worth the time as I have just about every spell published in official sources at my fingertips. Other than the initial investment of effort, the other downside is that Access isn't a standard part of home Office- you need to acquire it individually or as part of one of the more expensive Office suites. Access is also the Office program that nobody's done a good job of porting over to open source, so what you do in Access won't translate well to Open Office, Libre Office, etc.
I drive a quantum car- every time I look down at the speedometer, I get lost.
_____________
As a juggler, I may not always be smarter than a banana. However, bananas aren't often surrounded by children asking for hugs and autographs.
-
2017-02-21, 03:06 AM (ISO 8601)
- Join Date
- Feb 2007
- Location
- Manchester, UK
- Gender
Re: Help with indexing spell lists [Word/Excel/????]
There is a freebie equivalent of Access in OpenOffice/LibreOffice, as I recall, but I don't think it includes its own database engine--you'd need to install MySQL or something like that to handle that side of things.
-
2017-02-21, 04:32 AM (ISO 8601)
- Join Date
- Feb 2017
Re: Help with indexing spell lists [Word/Excel/????]
We do acronyms differently. ex. United States of America (USA). I do not even need the definition, just a list of the aacronym would be helpful. How would a macro for that look?
-
2017-02-22, 12:28 AM (ISO 8601)
- Join Date
- Jun 2009
- Location
- The One in the Middle
Re: Help with indexing spell lists [Word/Excel/????]
I drive a quantum car- every time I look down at the speedometer, I get lost.
_____________
As a juggler, I may not always be smarter than a banana. However, bananas aren't often surrounded by children asking for hugs and autographs.
-
2017-02-22, 03:02 AM (ISO 8601)
- Join Date
- Feb 2007
- Location
- Manchester, UK
- Gender
Re: Help with indexing spell lists [Word/Excel/????]
Interesting to know! Well, if LibreOffice Base *does* allow you to create a database file then it's worth looking at that--it'll take a lot of work to set up your spell lists as a database, but once you've done it getting those spells out in any order you like becomes trivial.
-
2017-02-27, 05:33 PM (ISO 8601)
- Join Date
- May 2007
- Gender
Re: Help with indexing spell lists [Word/Excel/????]
That looks like a fun project!
Some observations about Access:
1) If you did know your way around this tool, it would be the way to go.
2) You'd need to own a version of Office which comes with Access. Student versions should do this.
3) If needed, Microsoft also allows a free download of an Access reader tool ("Access Runtime"), which your friends can install to use this.
4) Access, despite being considered the easiest database for a layperson to use, still has a more than slight learning curve.
5) Unlike other Office products, Access is strictly for Windows. If you want something that runs on macbooks, use Excel or Word.
6) A very similar project for me many years ago eventually turned into a degree and career in database work.
TL;DR if you have lots of time and drive, you could pick up a marketable skill while making a neat tool in Access.
That said, I'm guessing you want to keep things simple.
By keeping things simple, I mean keep it all in Word, or better yet, Excel.
Loading the list into Excel allows you to use Excel features to do the heavy lifting. Dedicate one column for Class, one for Level, and one each for any other trait you wish to use to filter the list. Put a header row in with titles for each column, then switch on filtering. You can now easily, selectively hide irrelevant rows. If you want to automate some uses, try using the Macro recorder.
If you want to use Word, it is possible to do it that way as well. You'd want to put everything into code and create a custom form - a lot more effort than Excel.