Excel and Massive Campaigns

[Let's Greenfield]

Perhaps this is the wrong place-entirely-to be asking, but I've found NO help anywhere on-line. To y'all I can just describe what I want to do, without trying to figure out the most "normal" thing I could say without elliciting a groan. so, the parameters are 1: You play D&D(or an rpg), and 2: You know a good amount about Excel(and, really, 3: that this thread is posted in the right place).

My Greyhawk campaign is only a few years old, its only me, and my girlfriend, yet the amount of characters we run into, or play is immense. It've very GoT in Nature(everyone has an angle, and a motivation). The party is about 7 + large, and we have many fallen, and have been double-crossed, or promised many. It's very T.V.Tropes-and I'm proud of it; you pull in one direction, someone else get's ticked-off.

It's a lot to juggle, and it's a lot to remember whohas dealings with who when it comes times for nice plot "twists", or development. To that end, I'd love to figure out how I could best enter all of the names (even some places when relevant) of the campaign into individual cells. And just use some Conditional Values, or some other means, to determine, "Oohhhhh: Fletch and Rufus both have that Belsornig connection! I forgot. But Teck-Ed and Gerald both have an emnity with gnolls!" It would helo me immensely. My campaign is rich, and,,,scattered.

Do any of you know enough about this sort of data entry/collation that you could help me out a bit?

Thanks

 

[Dannyalcatraz]

Excel can be a powerful tool- I’ve ised it for HERO character sheet generation- but I’m not seeing how it would help in this case.

You might just want to keep flash cards- or generate some on PowerPoint- to keep track of the moving parts.

 

[Let's Greenfield]

Thanks, @Dannyalcatraz. That's a shame that a program that powerful can't be used for cross-reference. I've learned one way of doing it, but it's very, very cumbersome. If I do conditional modifiers, it will show me every instance of duplications, but that's only a little helpful; I want to take-what I presume should be-the column header[Character] and find out exactly who that character knows from other columns. Hmmmmph...

 

[Dannyalcatraz]

It can be used for that...if you have your cells set up right. I have my CD collection listed in Excel, so I can sort for artists, for year of release, genre, etc.

I’m just not seeing how it helps you in this situation. Could you expand on what the problem is you think it would help with?

 

[Umbran]

If I understand what you are saying, the issue is that a spreadsheet is, ultimately, a simple 2d representation of data, when what you really want for this is far more complex.

I mean, you can imagine a spreadsheet - the left column is the NPC name. The next few columns are standard statistics, and then after that you have columns for plots or personality elements. But for each NPC, the number of plot cells will be different, and they won't all be in the same order, so simple sorting won't avail you much.

What you probably want is a full-on searchable database, so you can say things like, "Look at Jack and Cornelius, and tell me what they have in common, if anything." That's more Microsoft Access than Excel.

But, ultimately, I think you'll find maintaining the data with sufficient fidelity to be really useful will be a pain in the butt. As soon as you forget to add stuff to the database, it effectively degrades just a bit, and eventually it no longer serves the purpose.

 

[Let's Greenfield]

Yeah, that's all spot-on, @Umbran. I really don't know MS Office-just started with it(I have been a complete luddite for a decade, or so)-so I was hoping I saw a solution to a new problem. I'll have to look into Access.

But, I also agree that the maintainence will probably wind-up being too much of a hassle.
" Could you expand on what the problem is you think it would help with?"
@Dannyalcatraz, "the problem" is that I'm essentially author of a giant soap-opera, more so than a rpg. Everyone is the campaign (Greyhawk) winds-up being affiliated with each character somehow, or is planting the seeds for such. As I put together more interesting encounters, or plot-threads, it becomes helpful to know that Character X actually ran into Character Y so many seesions ago. Or that it take two degrees of separation to get to Bad Guy X, that otherwise wasn't obvious to the players. My game sounds like it's based purely on plot-twists, but it's just shook-out that it's a massive conspiracy kind of thing.

And it's obviously a mile above my head.

 

[Umbran]

Something that might cover part of your needs, and is easier to work with - a wiki.

Imagine a page for each notable NPC, with notes. Also, with tags for each plot or relevant connection. Then, it is easy to get "all pages tagged with X". Is Obsidian Portal still up and around? It might prove useful.

 

[Tonguez]

wouldnt it just be an If-And Match across multiple worksheets?

So do a Master sheet listing the PCs and then extra worksheets for each character listing all their NPC connectionsand interest, then on the MAster sheet do a Match on the NPC names to identify all the PCs with common NPC connections.

 

[LostSoul]

SQL would be better than Excel, but you could probably do it like this:

Data Sheet
Column A: A list of all people, places, or things.
e.g.
A1: Rufus
A2: Fletch
A3: Belsornig
Column B: An id value. Unique numbers, starting at 1 with an increment of 1. You can write a formula for this; enter B1 manually as 1, then B2 is the formula =B1 + 1 and you can just copy paste all the way down.

Connection Sheet
Column A: A combo box with data validation that allows you to pick from all of Data Sheet!Column A. This one starts on row 2, A1 is empty.
B1: Data Sheet!A1. C1: Data Sheet!A2. D1: Data Sheet!A3. etc.

This will give you a table where the y axis and the x axis hold all of your entries. Then you can fill out your table by finding the corresponding matches.
e.g.

.......... Rufus      Fletch     Belsornig
Rufus      Me         Buds       Big fight
Fletch     Buds       Me         Big fight
Belsornig  Big fight  Big fight  Me

Main Sheet
A1: A combo box with data validation that allows you to pick from all of Data Sheet!Column A.
B1: A combo box with data validation that allows you to pick from all of Data Sheet!Column A.
C1: =VLOOKUP(Main!A1, Connection!A2:ZZ99, VLOOKUP(Main!B1, Data Sheet!A1:B99, 2) + 1)

You can do the same for other rows in the main sheet if you want to see multiple connections, just make sure you change the main sheet references in the lookup.

The ranges for the lookups are arbitrary so change them to suit your data (if you have more than 99 rows in the data sheet the lookup will need to reflect that).

There's probably a better way to do this so you don't have to enter the same data over and over again... Not sure how to do that, though. Copy-paste away!

Edit: There may be a much simpler way to do this.

Data Sheet
Column A: People, places, events, etc.
Column B: Some type of pre-defined data, e.g. Likes
Column C: As B, but some other kind of data, e.g. Dislikes.
Column D: As B & C, but something else, e.g. Belsornig.
etc.

Main Sheet
A1: Combo box with data validation for Data Sheet Column A.
B1: =VLOOKUP(Main!A1, Data Sheet!A1:B99, 2)
C1: =VLOOKUP(Main!A1, Data Sheet!A1:C99, 3)
D1: =VLOOKUP(Main!A1, Data Sheet!A199, 4)
etc.

 

[Eltab]

I think you will have to begin away from a spreadsheet: for each NPC, write down their "history". Review that notebook-full of facts for common points that you can use to format the spreadsheet pages. Then you will be ready to enter data into a searchable form.

It may help if you run searches on the classic radio/TV soap operas. They also had complex character-connection plots, and had to keep track of who did what when with/to whom*. If behind-camera staff have ever done an interview about "how we kept track of it all", it might give you some pointers or leads.

* Charles can't romance Rachel ! They are both love children from the same father (George) who had a one-night-stand with Linda in Season 25 and a torrid affair with Harriet in Season 27-28 !