How do I go about creating a Excel/Word indexed spell list?

[Aotrs Commander]

As preparatory gubbins for my next 3.x campaign (and because the blasted players haven't gotten me their nomnal backgrounds to work on today), I am looking into an overhaul of my current spell indexing system.

Currently, constructed at GREAT length, we have a list of spells for each character class - one table for every spell level - which has the short spell description and reference to the book and page of the original source (and school for Sor/Wiz spells).

Further, at equally great length, I have extracted (via copy-paste from PDF or by brute-force typing up) the spells from a large number of the splat books (and amended/updated a modest number of them). Principally, this was done to reduce the carryng weight of not taking the entire of 3.x with my in my 85-litre backpack to the club every week. (I have also done the same with the feats and a few other bits of the splats we actually use). These spells are currently located in a single spell descriptions document, sorted by splatbook.

As one of my hardcovers is starting to fall apart, I am now going to do that same to that. But I made the foolish mistake of asking my players if there was anything I could look at to improve said indexing system while I was at it, and they said "re-index all the splatbooks spells in the documents to your document page number, not the original source."

So, like, bugger.

There are three copies of each spell list - a master list, a generic list and one specific one for my own campaign world, so this is a job I don't want to have to do more than once.

So, my initial starter for ten will probably be alphabetising all the spells in the document, and creating a table of contents, which will at least give me an up-to-date page reference. (At this point, I could even fold in my own created spells into the document as well.)

What I am principally here to ask, then, from those as might know better than me, if there is a good way to somehow utilise a spreadsheet to partically automate the process, particularly to future proof this having to be done again? Doing it all manually is... not something I want to contemplate, as if anything is ever added, you'd have to go through every single spell list to update the page references for every spell!

I am not adverse to the final form ending up being some sort of copy-paste function, whih would not be too bad to deal with. E.g., one might copy paste the active list from the spells document into Excel as a reference. A make second copy of that a few columns over, which would the "business end" where you would be adjusting the page number column with a formula or function that would look up the new/current page number, so you could then copy-paste values back into the Word document. (Which would make updating the page references easier when/if more are added in.)

It is working out how I could obtain the page-number references from the spell description document to put in that I am unsure of how to sensibly approach. (I have some mderate experience with Excel - principall coming from creating a spreadsheet to assist the shipbuidling and pints-costing for my startship rules, which was moderately complex.)

Now, while I can see an arguement for the spell lists perhaps being transferred to purely spreadsheet form, I am less sure of how practical it would be to attempt the same for the actual spell descriptions, which is obviously something more suited to text format. What I don't know if there is any way to make Excel look up something from a word table of contents (or perhaps an index? I've not used word's indexing feature before.) (Again, it wouldn't matter if I had to copy-paste the table of contents/index list into Excel to extract the numbers.)

Any suggestions, guidelines or other input would be very welcome!

[evangaline]

Well, I'd suggest you take a look at what has already been done. There is the Ecyclopedia Vindiculum Draconis (http://www.minmaxboards.com/index.php?topic=11953.0), which contains an alphabetical list of all dnd 3.5 spells in excel and in word. It also contains lists of feats, familiars, summons, et cetera.

[Aotrs Commander]

Well, I'd suggest you take a look at what has already been done. There is the Ecyclopedia Vindiculum Draconis (http://www.minmaxboards.com/index.php?topic=11953.0), which contains an alphabetical list of all dnd 3.5 spells in excel and in word. It also contains lists of feats, familiars, summons, et cetera.

That doesn't have any page references, however, either to the original sourcebooks or itself, which is the principle part of the excercise. (Useful to know it exists, however.)

Otherwise, there is some chance it might have been quicker to add to/delete/amend the spells in their document to match our list (which is basically the list of approved spells, since we don't allow the use of the entirity of every spell that has ever exsisted in D&D (and/or Pathfinder) - plus at ovcer a thousand pages, it would sort of defeat the objective of cutting down the amount of paper carried... (Big files are all well and good when you can do a "find" with word, but not when you are using paper!)

(The spells document I'm using principally is only extracting spells from books which are mostly not spells (the completes/frostburn etc), because aside from the feats, spells and classes, we don't use the rest (with the exception of the odd PrC) and so about half the books are dead weight when you havwe to carry it all. The Spell Compendium spells are not included, for example, because that's all spells; and the PHBII we use the majority of.)

What I'm looking at is more than just putting the spells in alphabetical order in one place (that's pretty easy), it's indexing the spell lists for each class with the new page references for said place. (We have the old page references for the splatboos themselves already.) And in doing so, ensuring that if I find I have to add another spell somewhere down the line, I don't have to go through every spell list to change said page references.

[Teddy]

This sounds like you need an actual database, at least if you want something which is easily maintainable. A simple MySQL database with a main table indexed over spell names and a secondary table for the spells per level for each class should probably do. The page reference could be handled by a count function, possibly weighing each entry after the length of its description text. It should be relatively easy to export to Excel spreadsheets as well.

That said, using a database requires you to learn some basic SQL (or other database language), which can be a bit daunting if you're not used to either programming or command line interfaces...

[Heliomance]

You could try using SpellGen (http://pigmentia.net/spellgen/)? It's my favoured way of handling spells. Doesn't quite have every single source, but it's fairly easy to add to.

[Aotrs Commander]

This sounds like you need an actual database, at least if you want something which is easily maintainable. A simple MySQL database with a main table indexed over spell names and a secondary table for the spells per level for each class should probably do. The page reference could be handled by a count function, possibly weighing each entry after the length of its description text. It should be relatively easy to export to Excel spreadsheets as well.

That said, using a database requires you to learn some basic SQL (or other database language), which can be a bit daunting if you're not used to either programming or command line interfaces...

I can sort of understand the concept of what you're saying; except for the part where I stumble at "SQL" and "My SQL" which went straight over my head. And after a cursory google search, MySQL appears to be some form of database-y thing most commonly used to do website management or something? That... might be prohibitively expensive in terms of time to attempt, since my programming experience is limited to "did some very basic programming fifteen years ago as part of degree course that has been mostly forgotten," "minor scripting for odd games with editors" and Excel - the most complex thing being the aforemention points cost spreadsheet for my starship rules, which took me a good month or two of solid effort to work through. On top of the actual time to sort it all out, it's probably not something that is an efficient use of my time at this juncture.

(Among other things, I am actually intending to try getting said starship rules back into a publishable state this year and my rules/quest writing time is limited.)



You could try using SpellGen (http://pigmentia.net/spellgen/)? It's my favoured way of handling spells. Doesn't quite have every single source, but it's fairly easy to add to.

Had a look, but it's not really what I need - I already have the lists of spells, it's just I want to be able to change the page numbers on siad list to my new references.

(Thread title is not very clear - or good (and not to my usual carefully considered standard), I know, but unfortunately, I hit "post" before I'd properly sorted it and made a bit of frantic effort to edit it to something vaguely like before the thread-title-editing period expired...!)



Maybe if I show some pictures, it'll be clearer as to what I'm getting at...?

This is a screencap of one of the current spell lists I already have for all the classes (which also have some changes and additions/modifications denoted by the colours).
http://i168.photobucket.com/albums/u172/AotrsCommander/spellliststuff1_zpsu6mldugw.png

The main thing is those page references.

Many of them are now redundant because I've (at great length...) complied many of the splatbook spells into a single document file thusly:

http://i168.photobucket.com/albums/u172/AotrsCommander/spellliststuff2_zpsxcsi7t7t.png

And in doing so, made a fair number of corrections/updates/campaign world modifications (as seen here).

What I am trying to work out if I can do is to find a way to automate looking up the page number from that document to update the page number box on my spell lists. (By "automate" I mean "doesn't require me to manually go through all the spell lists to re-number all of the spell page references if I add Aardvark Storm somewhere down the line" - having to (say) copy-paste the lists out from a spreadsheet or something when I do need to do it would be fine, it doesn't necessarily have to be directly linked like, say, the points-comparison tables for my starship spread sheets are.)

The various programs for complete D&D sources are not really what I'm looking for (especially if they themselves only have the sourcebook and not the page numbers, which is less information than I already have!) as I would have to - assuming I even could - go through them and re-do all our modifications.

(I don't fancy having to re-do all the 3.Aotrs-buffed Cure/Inflict spells AGAIN, for example...!)

If there is no way to do what I want, I have said to the players that they'll have to live with something like just italising the page references in the spells lists (as short hand for "is in the spells document/booklet1") and just having that in alphebetical order as opposed to organised by splatbook as it is now - possibly with a table of contents/index.



If all else fails, I will perhaps have to see what I can scrape out of Word and Excel on Monday (which is Designated Quest Writing Day.)



1All of these lists are printed out, not used digitally, since our regular session takes place down the wargames club where there is no PC2.

2And the point of creating the spells document was 100% motivated by not havign to carry all books to said club...! Doing the modificationsas I was doing it was merely a no-brainer bonus.

[Tvtyrant]

I know this isn't what you asked, but why not tell them to shove it? If you already indexed everything one way then just keep it that way and offer them a copy if they want to reindex it. No reason to make more work for yourself...

[factotum]

That... might be prohibitively expensive in terms of time to attempt, since my programming experience is limited to "did some very basic programming fifteen years ago as part of degree course that has been mostly forgotten," "minor scripting for odd games with editors" and Excel - the most complex thing being the aforemention points cost spreadsheet for my starship rules, which took me a good month or two of solid effort to work through. On top of the actual time to sort it all out, it's probably not something that is an efficient use of my time at this juncture.

If you have Microsoft Office then do you also have Access? That's designed to be a simple-to-use database management tool, and while it's not got the raw power of a full-blown SQL server, it would take a lot less time to get up to speed with it. Alternatively there's some sort of database management tool that ships with OpenOffice/LibreOffice, but it requires connecting to a separate SQL server (e.g. MySQL) and is thus a lot more complicated to set up.

[Aotrs Commander]

I know this isn't what you asked, but why not tell them to shove it? If you already indexed everything one way then just keep it that way and offer them a copy if they want to reindex it. No reason to make more work for yourself...

Because I did make the mistake of asking them, didn't I?

To be fair, I have told them that if I can't find a way to do this, italising references (as a short cut to "in the spells document") and alphabetical order in said spells document is the best they will get!

(And I'm the only one with time/daft enough to do this sort of thing.)




If you have Microsoft Office then do you also have Access? That's designed to be a simple-to-use database management tool, and while it's not got the raw power of a full-blown SQL server, it would take a lot less time to get up to speed with it. Alternatively there's some sort of database management tool that ships with OpenOffice/LibreOffice, but it requires connecting to a separate SQL server (e.g. MySQL) and is thus a lot more complicated to set up.

I don't appear to have Access installed (it did cross my mind when databases were mentioned) - whether I just elected to not install it or I didn't get it when I bought my MSOffice 2007 pack way back, I don't know.

[factotum]

Not all Office installations come with Access, which is why I asked if you had it--it's a fairly specialised product. I think there are open source alternatives, but I have no idea how good they are and how easy it is to produce a working database model using them.

Word is capable of automatically generating a table of contents for a document--it's not something I've done myself, but it does sound like what you need if you can't go the full database route.

[Brother Oni]

Word is capable of automatically generating a table of contents for a document--it's not something I've done myself, but it does sound like what you need if you can't go the full database route.

In the version of Word I'm used to (2010), this requires using Headings or some other style formatting to mark where a new section begins so the automated TOC can pick them up, so it's still a fairly hefty manual job unless you have some automated way of selecting all the spell names in a single go and applying the style formatting that way.

It still doesn't make the list sortable though, it just makes a table of content in the order you have listed in your original document.

[Aotrs Commander]

I've done tables of contents before (quite familiar with 'em actually!) so I know I can do that part (that's may starter for ten, in fact). Just use the spell titles as headers, be easy enough! I think there is also an index system; though I've not used that personally, my Dad did when he was writing Manouvre Group.

A toc (or index?) would at least give you the page reference for the spells... If I can work out how to extract that information in Excel, it's easy enough to write a little formula to put in an excel sheets. Something like [FINDTEXTSTRING] spell name (all characters before ":") [LOOKUP] (string) on [table dervived from Table of contents], print "Spells p [page number]".

I would then take the lists I have, sort by column 3 (source and page reference), select all the requires sources and paste those lines into Excel, sort back to alphaetical order and then use said formula on the required cells, and copy it out back out. Relatively quick and easy.

The bit I'm not sure about is how/if I can extract the numbers from the ToC/index in Excel. (It might be possible to somehow use dig them out as text string, I'm not sure. Extracting text strings (including numbers) and looking them up on a table was a principal function of afirementioned points spreadsheet and I'm thus quite familair with how to do that. However, generally, there were one concrete signposts (brackets, usually) to define the start and endpoints. )For example, extracting the spell name from the current spells lists, it's easy, all I'd have to do is define the text string as "starts a 1st character, ends at (colon -1 character). Whether there is a way to the format of index or table of contents to be able to do something similar, I don't know; that is what I shall experiment with on Monday.)

[Aotrs Commander]

Well.

Looks like the MS Access thing is not gong to be an issue, since SMART detected a problem with the XP HDD today... So, before I attempt to fix it with Seagate tools, full back-up is requried (including Outlook Express email). Whcih meant also doing a check to see how you back OE to outlook, so I had to look for my MS Office thing and found we have

a) Office 2003

and b) the home set of Office 2007, which does not have Outlook.


So me and Dad have had to fork out for MS Office 2010 anyway (since both us are basically self-employed business persons at this point) and email is kinda critical. So whether or not the HDD can be repaired, (full back-up in progress, inclduing email and whatnot...) we will have MS Office for my Win 7 (=> Win 10 in a few weeks) and his laptop anyway. Something we'd have had to do eventually, and better to do it now while we can buy 2010 than have to pay subscription for 2016...

May somewhat delay the whole quest-writing time thing though, if I have to move everything over to Win 7!

*sigh*

Couldn't a waited a week or two, could it...