Jump to content

Excel People


wykikitoon
 Share

Recommended Posts

Come on then @@The Fish or @@Gemmill, I need your brain cells again.

 

I am trying to create a similar league position graph for the season to this one below.

I have managed to get it working to a point, however my Y Axis is going from 0 - 10 where I want it flipping so 1 is at the top.  I.e like the league table.  Any ideas?
 

tQyHGPi.png

Link to comment
Share on other sites

A simple work around would be to rename the positions 1st, 2nd, 3rd...19th, 20th etc. as text rather than number. 

 

I've done something like that before and I'm sure there's a better way but off the top of my head I can't remember.

Link to comment
Share on other sites

  • 4 weeks later...

Come on then @@The Fish or @@Gemmill, I need your brain cells again.

 

I am trying to create a similar league position graph for the season to this one below.

 

I have managed to get it working to a point, however my Y Axis is going from 0 - 10 where I want it flipping so 1 is at the top.  I.e like the league table.  Any ideas?

 

 

tQyHGPi.png

Right

 

Somehow my excel file got corrputed.

 

I have been trying all morning to try and replicate similar to above, but cant.  My heed is fucking fried man!

Link to comment
Share on other sites

  • 11 months later...

I've been mucking about trying to get a table that calls a number from a separate table based on two criteria. I'm convinced I need a nested vlookup, but I can't get it to return what I'm after.

Basically I want LEAGUE POSITION returned based on YEAR and MANAGER.

I've a table (CLF) with the club's league finish, and the year and the manager. And I've another table with transfers and stuff including manager a year.

I want the Transfer table to have the league position. So for every instance of Sam Allardyce in 2008 should return "11".

In my mind I want =vlookup(vlookup(manager, Transfer table, year, false),CLF,lg position, false).. but that's clearly wrong.

 

Would it be easier if I concatenate a column with "Sam Allardyce 2008", and vlookup off that one column?

Link to comment
Share on other sites

On 30/01/2018 at 08:35, The Fish said:

I've been mucking about trying to get a table that calls a number from a separate table based on two criteria. I'm convinced I need a nested vlookup, but I can't get it to return what I'm after.

Basically I want LEAGUE POSITION returned based on YEAR and MANAGER.

I've a table (CLF) with the club's league finish, and the year and the manager. And I've another table with transfers and stuff including manager a year.

I want the Transfer table to have the league position. So for every instance of Sam Allardyce in 2008 should return "11".

In my mind I want =vlookup(vlookup(manager, Transfer table, year, false),CLF,lg position, false).. but that's clearly wrong.

 

Would it be easier if I concatenate a column with "Sam Allardyce 2008", and vlookup off that one column?

 

Use index match match. Its basically vlookup's big brother. 

 

Have a look here, it'll walk you through how the index and match functions work separately, and then how they can be combined to do (I think) exactly what you're trying to do. 

 

https://corporatefinanceinstitute.com/resources/excel/study/index-match-formula-excel/

Edited by Gemmill
  • Thanks 1
Link to comment
Share on other sites

  • 3 months later...

@Ant

@Gemmill

I've been trying to teach myself VBA and I'm doing ok by reading stuff online and mucking about. But I can't find the code to do the following:

I paste some data into a range in Excel, then hit a button which launches the following Macro:

Sub Update()
'
' Update Macro
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    Range("Updated").Select
    Selection.Copy
    Sheets("Master").Select
    Range("Master1[Manager]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

but I want to paste the selection at the bottom of the "Master" dynamic table so that it expands the table t include the new data. Cheers thanks ta

Link to comment
Share on other sites

4 hours ago, Monkeys Fist said:

And him in top set as well. 

:D

Sorted it myself because I'm geet smart as fook

Link to comment
Share on other sites

  • 6 months later...

Got another one for you.

I have a number of cells with figures in.  I want these to be divided by another cell.

So I thought I could just put in =A1/B1 then drag down and it would do a whole load of numbers.  Problem is it changes B1 to B2, B3 etc.  The B1 is a constant, how do I do it so it A1/B1, A2/B1, A3/B1 etc without going through every single cell?

Link to comment
Share on other sites

I am sure that there is a good way of doing this, but I don't know it. So my cheap workaround way would be the following:

 

Put =A1-1 in the first cell. Copy it down - you'll end up with the A1 changing as it moves down, but the -1 will remain. Then highlight the column and go to find and replace. 'Find' -1, and 'Replace' with /B1.

 

That'll do it. It's fucking stupid, but that's how I roll :lol:

  • Thanks 1
Link to comment
Share on other sites

31 minutes ago, wykikitoon said:

Got another one for you.

I have a number of cells with figures in.  I want these to be divided by another cell.

So I thought I could just put in =A1/B1 then drag down and it would do a whole load of numbers.  Problem is it changes B1 to B2, B3 etc.  The B1 is a constant, how do I do it so it A1/B1, A2/B1, A3/B1 etc without going through every single cell?

Put B1 into the formula as $B$1

  • Thanks 1
Link to comment
Share on other sites

20 hours ago, ewerk said:

Put B1 into the formula as $B$1

 

If you're changing an existing formula, click on the cell reference in the formula bar and press F4.   This will add the "$" for you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.