EXCEL problem

**borgsta** · 05-27-11, 06:39 PM

IMO you are betting off using SUMIF argument and naming the tables.

So in your example you would name the table in Worksheet B from A1-Z1 as 'Pitchers'
Then name the table in Worksheet B from A5-Z5 as 'IP' (this is assuming you have pitchers named from left to right and their respective innings pitched also from left to right)

Then in Worksheet A you can use the following

=SUMIF(Pitchers,A1,IP)

A1 in this example is where the pitchers name is in Worksheet A.

If you need a mock draft I can upload an excel sheet or upload yours and I can play with it.

**Formulawiz** · 05-27-11, 07:47 PM

Attached is the excel worksheet. Sheet 1 has the pitchers name and I want to get the value from sheet 2 thats in IP cell B42 which is the innings pitched. what formula would I use. remember sheet 1 will have all the pitchers in MLB and sheet 2 will have all the ARI pitchers, page 3 all the ATL pitchers and etc. When sheet 2 updates the query some of the cells can move around.
Thanks

Attached Files

test.xls (23.5 KB, 206 views)

**borgsta** · 05-27-11, 09:04 PM

Ah ok, if it is using queries and moving cells around than the simple function I provided above won't work.

You will probably need a macro / formula going on to search for the value of IP and put it in a master table somewhere. A bit over my head unfortunately.

**RickySteve** · 05-28-11, 01:33 AM

Write a script to munge what you're looking for.

**mrtomk** · 05-31-11, 04:37 PM

Yea, you'll most likely need a macro for this. I'm pretty inexperienced with them but am trying to learn vba programming at the moment, so I thought Id have a stab at your problem, attached.

To view the code I've used, press Alt-F11. Ive tried to add a bit of explanation to it. There may be a more streamlined way of doing it, as I used a lot of trial and error to finally get there.

The macro should look through the sheet names for the club initials in column B, then find the pitcher name in that sheet, and look down and across the 41/1 rows/columns to give the IP stat. Itll then loop down through all the pitchers in the list.

Let me know how it goes...

Attached Files

test.xls (50.5 KB, 165 views)

**Formulawiz** · 06-01-11, 09:39 AM

Originally posted by mrtomk

Yea, you'll most likely need a macro for this. I'm pretty inexperienced with them but am trying to learn vba programming at the moment, so I thought Id have a stab at your problem, attached.

To view the code I've used, press Alt-F11. Ive tried to add a bit of explanation to it. There may be a more streamlined way of doing it, as I used a lot of trial and error to finally get there.

The macro should look through the sheet names for the club initials in column B, then find the pitcher name in that sheet, and look down and across the 41/1 rows/columns to give the IP stat. Itll then loop down through all the pitchers in the list.

Let me know how it goes...

It works fine but there has to be a way to do it with excel functions such as HLOOKUP or etc.
Thanks for your help but I dont understand programming. There are many other stats I want to capture besides innings pitched (IP).

**tomcowley** · 06-01-11, 11:00 AM

Originally posted by Formulawiz

WORKSHEET B has IAN KENNEDY in cell A1
Cell A5 has IP
CELL B5 has the value 74.1 innings pitched.

Really?

**tomcowley** · 06-01-11, 11:26 AM

Good god. Excel isn't made to handle trash like that, much less trash that isn't even going to be in the same cells consistently. Extract what you want, then use excel.

**mrtomk** · 06-01-11, 01:43 PM

If it was as simple as an hlookup, you'd have done it by now. Or, at least, someone else would've done it for you by now.

See formula in attached sheet (for some reason when I pasted it in here it left spaces in the formula and it wouldnt work when I tried to copy it back to excel)

Word of warning, if you are going to use formulae similar to the above for lots of other stats, for all the players, on all the teams in baseball, and keep them in the same workbook as all your data, your file is going to be enormous; Excel will shiit itself, literally, out of fear at the file size, and as a dirty protest against what you are asking from it.

As the poster above mentioned, you're life would be a lot simpler if you structured your data in a nicer way .

Attached Files

test.xls (51.0 KB, 176 views)