I need some help with Excel

[confuddledDOTcom]

On Monday a group of us decided to play Munchkins (card game) and for a laugh we invited a friend to play with us over Skype, which was totally crazy but good fun. I decided that instead of her having to write the cards down in future I'd make her a spreadsheet with all the cards and she could just bring up the right ones when she needed.

Sheet 1 has space for all the information about her hand, what level she's on the monster she's just drawn and a D6.

The other sheets list all the possible cards that can be drawn.

What I want to do is have it so she can enter the name of the card and it fills in the description of the card.

I used to do parties and I had a spreadsheet that was similar to give me a random set of quiz questions each time, but it's been so long since I did this I can't remember how to do it and far too long ago to still have that file or the ex that helped me make it.

You probably want to use VLOOKUP().

For example, have a separate sheet that lists the name in column A and the description in column B. Ideally, select the whole array with the names and descriptions in it and give that a name (but you can still do it by just referencing the array). Sort this array by name.

Then on your main sheet if you have, for example, the name of the card in cell E1 and you want to pull the description into cell F1, you need the formula in F1

=VLOOKUP(E1,Sheet2!$A1$B100,2,FALSE)

(at least, I think that's the right syntax. I've been using OpenOffice for a while so I'm out of practice with Excel. Look at the help file for VLOOKUP if it doesn't work)

Pach took the words out of my mouth. You want to do =VLOOKUP
Your data you're looking at needs to be in the leftmost column and in order.
In the first part of the formula you want to click the column you're looking for the data in, then comma between then click on the sheet where your data is, then select the columns where the information is, then comma again and then finally the number of rows along where the information is. It's really hard to explain so you might want to google VLOOKUPs but the most important thing to remember is the data has to be in the leftmost column and in order. Good luck!

There should be a colon in Pachacuti's example and possibly more dollar signs (ie $A$1:$B$100)

But I do feel slightly churlish picking on an excellent explanation

Precision is important, CoffeeandScones .

Spot on with VLOOKUP and C&S's syntax correction.

Your data you're looking at needs to be in the leftmost column and in order.

The lookup table array doesn't need to be ordered when requesting an exact match (declaring FALSE as the fourth argument), however values do need to be unique; data only needs to be in ascending order when requesting an approximate match (fourth argument as TRUE).

Meant to add: rather than typing in the name of the card, you could set a validated list using column one of the table array - so your friend can pick the option from a dropdown box instead of typing.

Apologies if I'm teaching granny to suck eggs.

You're all brilliant! Just got home so about to have a go at this. I've just tidied up how it's laid out as there are several card types (anyone else play Munchkins?) so I've tried to standardise them a little.

Not sure how to do the list, there's 125 cards so not sure if it'd be too many?

Worth Googling to see if someone's already done a list you can C&P.

I'm confusing myself, I think I'm almost sorted out what I'm doing but can you have a look at what the sheet looks like.

A1 A2 A2 A3 A4 A5 A6 A7
Name / Type / Lvl / Description / Description / Description / Value

Name would be where she types in what card she has, three description fields and value is either how many credits it's worth or how much loot the monster drops.

Not sure how to do the list, there's 125 cards so not sure if it'd be too many?

It won't be too many for the VLOOKUP, the names in column 1 just need to be unique.

Why three description fields?

You could try contacting the OP here, who seems to have been trying something similar, and see whether he/she would share a copy of the workbook with you.

Pachacuti, it's different things for each card. A monster has a description, Bad Stuff and how many Treasure cards it's worth, clothing and weapons have description, some have some sort of clause and how many credits it's worth. On the first sheet I've got more appropriate labels for wherever they're called up.

Does look like they're doing the same thing, depends what game it is though, which has made me think about adding in new sheets to cover all the games we have between us. I'm a sucker for punishment!

WM, it's not that it's too many for the file, it's just the looking in the list for the card could take longer than typing it that would worry me.

WM, it's not that it's too many for the file, it's just the looking in the list for the card could take longer than typing it that would worry me.

We have a similar spreadsheet format at work for looking up insurance companies and their relevant contracts, from a list of nearly 800. If you order the list alphabetically and then insert rows for "B", "C", "D" etc at the start of that part of the list, you can type the appropriate letter in the field before using the dropdown box - this takes you to that part of the list and speeds up selecting the required entry.

It's a shortcoming of Excel's list validation that you can't click to show the list first, then start typing to take you to that part of the list, as is common in other applications using dropdowns - entering a letter first gets round this shortcoming.

That's a good idea, thanks

It's all working, thanks all for your help!

I haven't managed to get the drop down though, I looked it up and I can't find the options for it.

Google 'Data Validation'.

If you're using Excel 2007 or later it's on the Data tab. 2003 or earlier and I can't remember

If it doesn't help and if you want, I can take a look at the file and put the validation in and talk you through it - drop me a message and I'll give you my email address. It sounds like you'll be able to sort it though.

Sorry, google 'excel data validation' - I've just tried the search term above and it's not as useful as I thought it was going to be.

I'm using 2010. I watched some videos on YouTube but don't seem to have the data tab on mine which is very frustrating.