If function to fill next cell

[wizzer26]

I'm I only really know basic excel but I'm trying to set up a spreadsheet to record football teams and results as games occur

Arsenal win
Man u draw
Chelsea lose
Arsenal lose
Chelsea draw

I then want on another sheet to record all this information by team i.e (based on the data above) to see how many games have been played an the results

Arsenal. Win. lose
Man u. Draw
Chelsea. lose. Draw.

I can use lookup function to search cells for team and results but I'm stuck on how to make it search a range but ignore previously recorded results.
Can this be done?

 

[MartyS]

I'm I only really know basic excel but I'm trying to set up a spreadsheet to record football teams and results as games occur

Arsenal win
Man u draw
Chelsea lose
Arsenal lose
Chelsea draw

I then want on another sheet to record all this information by team i.e (based on the data above) to see how many games have been played an the results

Arsenal. Win. lose
Man u. Draw
Chelsea. lose. Draw.

I can use lookup function to search cells for team and results but I'm stuck on how to make it search a range but ignore previously recorded results.
Can this be done?

You could put your data into a worksheet, as follows:




[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Team[/TD]
[TD="class: xl64, width: 64"]Outcome[/TD]
[/TR]
[TR]
[TD="class: xl63"]Arsenal[/TD]
[TD]win[/TD]
[/TR]
[TR]
[TD="class: xl63"]Man U[/TD]
[TD]draw[/TD]
[/TR]
[TR]
[TD="class: xl63"]Chelsea[/TD]
[TD]lose[/TD]
[/TR]
[TR]
[TD="class: xl63"]Arsenal[/TD]
[TD]lose[/TD]
[/TR]
[TR]
[TD="class: xl63"]Chelsea[/TD]
[TD]draw[/TD]
[/TR]
</tbody>[/TABLE]


With it stored in two columns, as above (one for Team and the second for Outcome), it's very easy to create a PIVOT table.

Highlight the two columns

Go to the Insert menu and click on Pivot Table


The PivotTable dialog box will be pre-filled because you already highlighted the two columns.

Make sure New Worksheet is selected then click on OK.


A new worksheet will be added. On the right (usually) is the PivotTable Field List.

Drag TEAM to Cow labels
Drag OUTCOME to Column labels
and DRAG OUTCOME again from the top area (Choose Field to add to report area) into VALUES



Now, as you add data to the original sheet, you can easily go to the pivot table and click on any cell... then choose OPTIONS from the menu (will be under the highlighted PIVOTTABLE TOOLS section) and select the REFRESH option.



Hope this helps.