MS Excel/maths advice...

daytona0

null entry

cookie365

Let someone else do the work

https://www.google.co.uk/search?q=football+fixtures+generator+excel&ie=utf-8&oe=utf-8&gws_rd=cr&ei=5DGQVqjbAsP_UqGFhbgL

VfM4meplse

I've got a question if anyone is good with .xls?

I'm subtotalling from a filtered spreadsheet using the sum function / formula bar, and although it picks up the formula perfectly there is no entry in the recipient cell, even when the filter is set to "all". The spreadsheet is a download from Santander, how do I get it to display the sum of the selected cells?

Very weird, as I was able to do this with First Direct statement downloads on a separate worksheet in the file. (Yes, its tax return time ). Have played with the cell formatting function without success. Any ideas?

Eta: the more I play with it, the weirder it gets....I'm able to link between all other worksheets in the file, but wasn't able to move / copy the Santander sheet in so copied the data and pasted manually either. Am starting to think this is something to do with the origin of the data, even though its been pasted into a "normal" , xls file.

agrinnall

What format are the cells you are trying to sum? Are any of the cells a different format to others? Have you tried making the entire column the same format (number of some sort probably)?

Le_Kirk

Is/are the cell(s) you are trying to write to "Protected?"

rmg1

daytona0 wrote: »

Hey all, just a quick one regarding Microsoft Excel/maths...

I'm doing a football league spreadsheet whereby 22 "teams" play head to head games for 21 weeks (so 21 games in total - half a season). My teams are listed vertically from 1 to 22.

My weekly fixtures are listed vertically too (in pairs) so I have it like this:

Week 1:

---

1 Newcastle
2 Liverpool

---

3 Chelsea
4 Man U

---

etc

I obviously want it so that each team doesn't play the other twice. Does anyone know of any way to generate the complete list of unique pairs easily (all 21 of them, each as a vertical string)? I am currently filling it in by starting with the 1-2, 1-3, 1-4 etc at the top and then slotting fixtures in but this is extremely time-consuming.

This was actually quite straightforward (easy when you know how)
In cell A1, put the maximum number of teams you have (i.e. 22)
In cell B1 put 1 (first team)
In cell C1 put 2 (second team)
Now for the fun bit.....
In cell B2 you want the following formula:-
=IF(C1+1>=$A$1,B1+1,B1)
In cell C2 you want:-
=IF(C1+1>=$A$1,B2+1,C1+1)

Drag down as necessary.

It looks good at first glance but I haven't tested it completely.

To get the team names, you can do lookups as usual.

VfM4meplse

agrinnall wrote: »

What format are the cells you are trying to sum? Are any of the cells a different format to others? Have you tried making the entire column the same format (number of some sort probably)?

Le_Kirk wrote: »

Is/are the cell(s) you are trying to write to "Protected?"

Thanks guys, but I now certain the issue is with the source format. As I said, I couldn't copy the whole worksheet over in the usual way. This was because the file didn't recognise that I had any other .xls files open, when in fact I have 3 others on the go, all of which have no trouble in recognising each other! When I then highlighted the whole spreadsheet to copy and paste special, the paste options were web-based and not what you would usually see.

I then wasted my time copying the data into a spreadsheet that does sub-total, refiltered and picked up the cells I wanted - again included in the formula, but not the sum! Definitely something to do with the functionality of the source data. Santander is such a PITA, why can't they just use Office 10 like everyone else?