wykikitoon 20105 Posted January 4, 2017 Author Share Posted January 4, 2017 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? Link to comment Share on other sites More sharing options...
The Fish 10837 Posted January 4, 2017 Share Posted January 4, 2017 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 More sharing options...
wykikitoon 20105 Posted January 4, 2017 Author Share Posted January 4, 2017 Just found it. There is a check button that does them in reverse order. Link to comment Share on other sites More sharing options...
The Fish 10837 Posted January 4, 2017 Share Posted January 4, 2017 Just found it. There is a check button that does them in reverse order. excellent, where the fuck is that? Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted January 4, 2017 Author Share Posted January 4, 2017 Under the format Axisfree upload image Link to comment Share on other sites More sharing options...
The Fish 10837 Posted January 4, 2017 Share Posted January 4, 2017 Ah, cheers Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted January 30, 2017 Author Share Posted January 30, 2017 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? 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 More sharing options...
Gemmill 44771 Posted January 30, 2017 Share Posted January 30, 2017 Always keep a back-up. Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted January 30, 2017 Author Share Posted January 30, 2017 Cock Link to comment Share on other sites More sharing options...
The Fish 10837 Posted January 30, 2018 Share Posted January 30, 2018 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 More sharing options...
Gemmill 44771 Posted February 2, 2018 Share Posted February 2, 2018 (edited) 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 February 2, 2018 by Gemmill 1 Link to comment Share on other sites More sharing options...
The Fish 10837 Posted May 4, 2018 Share Posted May 4, 2018 @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 More sharing options...
Monkeys Fist 42339 Posted May 4, 2018 Share Posted May 4, 2018 And him in top set as well. Link to comment Share on other sites More sharing options...
The Fish 10837 Posted May 4, 2018 Share Posted May 4, 2018 4 hours ago, Monkeys Fist said: And him in top set as well. Sorted it myself because I'm geet smart as fook Link to comment Share on other sites More sharing options...
Gemmill 44771 Posted May 4, 2018 Share Posted May 4, 2018 I don't speak VBA. Set about trying to learn it a couple of times, and both times realised that only my employer would see the benefit so why fucking bother. Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted November 27, 2018 Author Share Posted November 27, 2018 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 More sharing options...
Rayvin 5217 Posted November 27, 2018 Share Posted November 27, 2018 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 1 Link to comment Share on other sites More sharing options...
ewerk 30528 Posted November 27, 2018 Share Posted November 27, 2018 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 1 Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted November 27, 2018 Author Share Posted November 27, 2018 7 minutes ago, ewerk said: Put B1 into the formula as $B$1 Champion, thanks! Link to comment Share on other sites More sharing options...
Rayvin 5217 Posted November 27, 2018 Share Posted November 27, 2018 Suuuure, if you want to do it the proper way. Pff. Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted November 27, 2018 Author Share Posted November 27, 2018 Meh, it works for me But please, do tell...... Link to comment Share on other sites More sharing options...
Rayvin 5217 Posted November 27, 2018 Share Posted November 27, 2018 I did, earlier. Before ewerk's post. It does actually work Link to comment Share on other sites More sharing options...
wykikitoon 20105 Posted November 27, 2018 Author Share Posted November 27, 2018 Man, I need to stop wanking. How did I miss that?! Cheers Link to comment Share on other sites More sharing options...
Rayvin 5217 Posted November 27, 2018 Share Posted November 27, 2018 Do it ewerk's way though, obviously. Link to comment Share on other sites More sharing options...
Leon 13 Posted November 28, 2018 Share Posted November 28, 2018 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now