[Help] Any Excel experts?

maffew said:

Probably the most efficient way to tackle Q1 is to have a reference table called say, menu, which is just a list of all the teams
Then use Hyperlink function to navigate to the worksheet of the same name eg =HYPERLINK("#"&A2&"!",A2)

Reason I would use the lookup list in column A us so you can use it as a variable in case it needed else where in the project

The above could equally be hardcoded for each team like so
=HYPERLINK(Arsenal!A1,"Arsenal")
You could also create a link on each teams sheet to take you back to menu

View attachment 95792

I think that is what you are after anyway
Question2, um where does the data come from and how is it updated/entered?

Click to expand...

Many thanks.

In relation to Q2, I (labouriously) have entered all the data by hand from the Companies House website for all clubs for the last five seasons (or more for some clubs), so it is nearly all hard coded, although some figures are formula based.

El Presidente said:

Many thanks.

In relation to Q2, I (labouriously) have entered all the data by hand from the Companies House website for all clubs for the last five seasons (or more for some clubs), so it is nearly all hard coded, although some figures are formula based.

Click to expand...

If the data on a website is an HTML table (more later) Excel can easily import it using the new Power Query functionality.

If you have the latest Excel you can import from webpage, if you have the older versions there is a free plug in from MS that adds a new tab called Power Query.

Excel simply looks for "tables" and brings them in. You have the option to simply import or "clean up" first.

Once set up you simply hit refresh for any updates.

Power Query is quite revolutionary. It can connect to a number of sources and merge them together in ways you never thought possible with vanilla Excel.

In most cases you barely need to write a formula again and you can leave the awful VLOOKUP and MATCH functionality behind for ever.

If you like writing formulas you can easily transition to its new in-built language. Power query is simply:

1) more powerful

2) executes much quicker

3) allows you to play with much bigger data sets.

El Presidente said:

Many thanks.

In relation to Q2, I (labouriously) have entered all the data by hand from the Companies House website for all clubs for the last five seasons (or more for some clubs), so it is nearly all hard coded, although some figures are formula based.

Click to expand...

Yeah, what clapham_gull said is a good option (or you can use more old school web queries with Excel which more people would be familiar with), and companies house has an API where I imagine you can pass in the company registration number as a variable (from Excel possible or using JSON I guess). I just did a quick test retrieving some data from the site but couldnt see any information regarding financials on the page I was on. I'll have a play, something to do like If you have a list of companies house registration numbers for the clubs that would be handy, PM them to me. (and should make up column E thus far on your 'menu' worksheet in fact I would change that sheets name to Config or Control or something)