Help with Lookups

[reicherb]

I’m trying to develop a spreadsheet (attached) for T-Ball that will let me setup lineups ahead of time for each game that I can bring to the game with me and that will easily make sure kids get to play each position equally and bat at each position in the lineup equally (per the rules). I’m stuck on a couple of calculations.

1st:
On the game workbooks (Game 1, Game 2, etc) there is a spot for me to put what position each player will play based on the inning. The number of times they play is correctly calculated on the summary sheet. I’d however like to put the position name next to their name in the batting order. For example in game 1, I’d like it to say Pitcher, 1st Base, Short Stop, and Center Field next to Brady who is batting 1st. I thought I had it figured out by using HLookup and putting the position between each row. It works for Brady during the 1st inning but doesn’t work anywhere else. What am I missing?

2nd:
On the summary sheet I’d like to be able to add up the number of times each player bats in each order. I’ve got it by using count if and selecting the specific cell that the players name would be in if they were playing first base and then adding all of the count ifs together. This could be a very tedious process to setup for a dozen games though. Is there a way that would work better that would like me copy and paste the formulas on the summary and not have to manually update the range for each range? I can copy down once I have each of the orders setup but I can’t figure out how to copy across.

Thanks,

Brad

[Richard Buttrey]

Hi,

You might find the attached pivot table approach offers you much greater flexibility when it comes to analysis. I have added formulae to your Game 1 & Game2 sheets but then realised that with the data re-ordered as I show on the Data sheet that the Pivot Table would be the obvious answer. See New Summary sheet.

Since it's a Pivot Table youj can drag and drop the filed names around to see different summary views of the data.

[reicherb]

The formulas you provided work great. I can't figure out how to recreate the pivot table though and the table doesn't change when the data changes. Why not?

Thanks,

Brad

[Richard Buttrey]

Hi Brad,

The PT can be refreshed by right clicking anywhere in it and selecting 'Refresh'.

This was only an example of course. Normally I would create a single line macro that automatically refreshes the PT whenever you activate the sheet to look at it.

Don't be put off by pivot tables. They are extremely quick to create, a matter of seconds. Try experimenting with them, this is the only way to get to grips with them. Start at the Ribbon with the Insert tab and selecting the Pivot Table icon and follow it through from there. Believe me it will be far more useful for this task than writing and maintaining formulae on separate game sheets.