Question for Excel experts

[GagHalfrunt]

This is something I'd normally do in PHP and MySQL, but it would be way easier if it could be done in a spreadsheet rather than writing an app. I'm not an Excel user, so either this is stupidly easy or I'm stupid to ask because it's impossible

Is there a way to make Excel search for results in one cell and then add or change the results in another cell based on a match?

IE:

- Have peoples last names stored in column B
- Search for the last name Smith which will hit on B17, B64 and B112
- Add or change information in cells C17, C64 and C112 to correspond to the last name matches?

Yes? No? Maybe? Is this thread now about pie?

 

[quakeworld]

maybe you could try this?

=if(b2:b255 = "Smith",b2,c2)

post the formula in a new column (say column E)

it searches a range in column b (b2 to b255) for the text "Smith" and if it matches it'll put "Smith" in column E. if it doesn't match it leaves the value found in column C.

then you could just copy and paste the info from column E into column B. i'm sure someone else will come up with a more efficient method.

 

[TSDible]

This is something I'd normally do in PHP and MySQL, but it would be way easier if it could be done in a spreadsheet rather than writing an app. I'm not an Excel user, so either this is stupidly easy or I'm stupid to ask because it's impossible

Is there a way to make Excel search for results in one cell and then add or change the results in another cell based on a match?

IE:

- Have peoples last names stored in column B
- Search for the last name Smith which will hit on B17, B64 and B112
- Add or change information in cells C17, C64 and C112 to correspond to the last name matches?

Yes? No? Maybe? Is this thread now about pie?

This is quite doable using VBA.

What information do you want to add or change in column C?

 

[TSDible]

Also, how are your names formatted in B? Is it just last name? Lastname, Firstname? Firstname Lastname?

etc.

 

[GagHalfrunt]

This is quite doable using VBA.

What information do you want to add or change in column C?

Think of it as scorekeeping for a fantasy golf league. Player picks 4 guys in the field, gets their earnings for the week. So assume the spreadsheet would be like

B C D E F G
N1 E1 N2 E2 N3 E3

Where N1-N3 are the picks and E1-E3 are the points/earnings for the week. So say McIlroy wins, Woods is 2nd, Mickelson 3rd, I want something to search and say enter $1,000,000 in the C column for everybody that has McIlroy in the B column, $500,000 in the E column for every hit on Woods in the D column and $250,000 in G for every hit of Mickelson on F.

 

[TSDible]

Would you want to keep adding to the "winnings" column with each iteration?

Say I had $500,000 this week and next week I get $250,000... would you want it to display $750,000 or do you just want to replace with $250,000?

 

[TSDible]

Do you actually have a demo of the sheet you can put somewhere?

 

[Pia]

Think of it as scorekeeping for a fantasy golf league. Player picks 4 guys in the field, gets their earnings for the week. So assume the spreadsheet would be like

B C D E F G
N1 E1 N2 E2 N3 E3

Where N1-N3 are the picks and E1-E3 are the points/earnings for the week. So say McIlroy wins, Woods is 2nd, Mickelson 3rd, I want something to search and say enter $1,000,000 in the C column for everybody that has McIlroy in the B column, $500,000 in the E column for every hit on Woods in the D column and $250,000 in G for every hit of Mickelson on F.

Put the week's player/payoff data in a separate table, do a VLOOKUP to that table from your C column aaaaand you're done.
http://www.techonthenet.com/excel/formulas/vlookup.php

 

[PowerEngineer]

Yes, this is quite doable. No need to resort to VBA.

I'm sure there's a way to do it via VLOOKUP as Pia suggests.

My personal leaning (as a matter of artistic flare) would be to use SUMIF. To do this, I would set up a second table with player names in the first column (say AA) and weekly earnings (or winnings?) in subsequent columns say (AB through AZ). The formula in cell C1 (i.e. "E1") would be something like SUMIF(AA:AA,B1,AB:AB), where the contents of B1 is "N1". What this puts in C1 is the sum of all values in Column AB where the value in Column AA in the same row has the value in cell B1 (i.e. N1). Obviously this formula can be morphed for each player and each week. (If your weekly results are going to be in rows, then you might want to rotate the player/winnings table I described above).

Make sense?

 

[TSDible]

I agree it can be done with a formula....

My concern was with the "Add or Change" information... you can't "add" information in a formula... Something would have to remain constant...

 

[PowerEngineer]

I agree it can be done with a formula....

My concern was with the "Add or Change" information... you can't "add" information in a formula... Something would have to remain constant...

True, but the results of the SUMIF formulas will change (when recalculated) after he adds or changes entires in the (second) "results" table I described.

 

[TSDible]

True, but the results of the SUMIF formulas will change (when recalculated) after he adds or changes entires in the (second) "results" table I described.

Yes I get that.

My static concern was that normally in a fantasy league... you have trades and such. The Sumif will only work if participants keep the same players throughout the league season.

The SumIF could also be used if you wanted to do a weekly (or some other period) payout, and you created a separate sheet for each one...

There are many ways to do it really.