Any Excel experts out there?

Miscellaneous and Personal Stuff I Must Share
1

OK, this is a little hard for me to explain but here it goes:

I have 2 worksheets in a workbook.

The first sheet has four columns. They are called: Player, Round, Pick, and Overall. Player is just text (a person’s name), and the other 3 columns are all integers.

The sheet has two columns. They are Player and Draft. Player is just a person’s name (the same as in the first sheet), and I’m trying to make a formula for the Draft column.

What I want the formula to do is take the information from the Round, Pick, and Overall columns, and to combine it into one cell. I would want to display in the form Rd X, Pk Y, (Z). X,Y,and Z represent the values in the round pick and overall columns from the first sheet. An example of an output I’d be looking for would be “Rd 2, Pk 4, (16)”.

Basically, I want to be able to type in a player’s name on the second sheet, and have the round he was picked, the pick in that round, and the overall pick he was show up in the next cell.

I’m pretty sure I either know, or could figure out, how to make this work if I wanted to use 3 different columns on the second sheet, but my real question is if there is a way to format so it all comes out in one cell.

I realize that this probably doesn’t make much sense, and I’m having a real hard time explaining myself. I’ll try to clarify if anyone has any questions, and if this is too confusing, just ignore this whole post.

Thanks

2

I’ve programmed multi-million dollar projects using Excel, but I don’t think this is an Excel thing. Have you tried a database?

Tripler

3

I definately agree with Tripler on that one. Use a DB and SQL to do it.

Or if you gush at the thought of using excel then I would recommend using a macro rather than a formula. Formulas are very limited, esp. when it comes to loops and such.

4

Yeah, the 3 column method would be a simple vlookup…if the columns are adjacent, you could just change the column index, no problem. Don’t know any way to get 3 things to display in one cell in Excel

5

I didn’t even think about a database, but I guess that sounds like what I would need.

Actaully, this is just for a stupid fantasy baseball draft that I’m conducting, and this whole thing isn’t even really necessary for the draft. I can just type in the information by hand if I need to. I guess I just got a little carried away here.

Thanks for your input everyone.

6

Now, I’m usually the first guy to suggest that a spreadsheet is not always the best choice but this ought to be real do-able in Excel using the string handling functions. Convert your values to text, =TEXT(B5,“0”), and assemble all the little strings into one big one. Unfortunately, my Excel is 4.0 and it doesn’t have any string concatenation commands. I assume that later versions have it because this can be done in a MS Works spreadsheet using their STRING function. My software at work is more modern, so I’ll try there.

Damn, this would be SO EASY in BASIC!

7

Bite your tongue! You have stumped Tripler, who has “programmed multi-million dollar projects using Excel” and is probably working out the solution RIGHT NOW, and you’ve nearly stumped me in a vain attempt to seem like I know something about Excel. That was not a waste!

8

Excel 2000 and probably 97/95 are more than capable of doing something like this.

In sheet 1, have cells A1:D1 say: “player, round, pick, overall” In cells A2:D2 input the data: “Larry, 3, 2, 6” and continue for A3:D3, etc.

In sheet 2, have cells A1 and B1 say: “player, draft” In cell A2 copy the player’s name or use the formula:

=Sheet1!A2

In cell B2 use the formula:

=CONCATENATE("Rd ",Sheet1!B2, ", Pk ",Sheet1!C2, “, (”,Sheet1!D2, “)”)

You can then use autofill to duplicate the formula for all players.

Unless I’m misunderstanding what you’re asking for, this should do it. No need for databases, loops or any fancy macros.

9

It’s much simpler than that. Just type


="ROUND "&Sheet1!B2&", Pk "&Sheet1!C2&", ("&Sheet1!D2&")"

The ampersand concatenates, and the cell contents don’t have to be strings. I’m using Excel 97.

10

Whoops. I’ve just realised you want to type in a player’s name and lookup the “result”. This would require the following:


="ROUND "&LOOKUP(A2,Sheet1!A2:A4,Sheet1!B2:B4)&", Pk "&LOOKUP(A2,Sheet1!A2:A4,Sheet1!c2:c4)&", ("&LOOKUP(A2,Sheet1!A2:A4,Sheet1!d2:d4)&")"

This is not the most elegant way to use Excel, but I’ve cut and pasted and it works!

11

Check out the CONCATENATE function. Look it up in the help file; it combines entries from several cells and returns a value based on that.