Karl Posted September 8, 2020 Posted September 8, 2020 Hi guys & ladiesI know many of you are using spreadsheets to record your poker data, and I'm hoping that someone out there may be able to help me with a formula.Simple spreadsheet, Date, Game, Buyin, Win/Lost, profit and Total.As I enter data, the total field is updated which is my bankroll, basically I want to copy the last current input of "Total" into another data field, so that I can use that area to display onto my Twitch stream.As I know the Total will fluctuate up and down, and I just can't find a formula to just grab that last data input to display into another field.Thanks for any helpKarl
Uni-Rep Posted September 8, 2020 Posted September 8, 2020 Hey @Karl. Not sure how tech-savvy you are, but this is how I'd do it.First, if I've understood you correctly, you got a total column (column f), which is a running total, and the last value in column f will be the current total bankroll. If you want to get the last value in this column/table, you can use a simple offset formula like this: =OFFSET(F1,COUNTA(F:F)-1,0)Now, I don't know how you're planning to pull it into your Twitch stream? You could of course just capture that particular part of the screen, but I don't think that's the GTO solution. Instead, I'd make Excel write this cell value to a txt file. You then have this txt file content imported in for instance OBS (when you add text, you also have the option to read from file).It's been a while since I've worked with VBA, and I'm definitely no expert, but you can do it with the following code:Sub WriteCellValueToTXTFile()Dim strFile_Path As StringstrFile_Path = "C:\Users\User\Documents\test.txt"Open strFile_Path For Output As #1Print #1, Worksheets("Sheet2").Range("A1")Close #1End SubThis code basically takes the content of cell A1 on the sheet "Sheet2" and writes it to the txt file just above. In this example I'm assuming you've got the data table on sheet1, and just want this value for stream content on a separate sheet. Now, if you want this code to run every time (so update the value in the .txt file) a change is made on Sheet1, just add the following VBA to the sheet:Private Sub Worksheet_Update(ByVal Target As Range)Application.EnableEvents = FalseWriteCellValueToTXTFileApplication.EnableEvents = TrueEnd Sub Check the latest poker release notes. Have a look at our poker promotions.
Uni-Rep Posted September 8, 2020 Posted September 8, 2020 If the above is like latin to you, feel free to just send me the sheet and the file path of your text file, and I'll quickly make the changes for you :) Check the latest poker release notes. Have a look at our poker promotions.
FeelsBadMan Posted September 8, 2020 Posted September 8, 2020 Another option for showing it on OBS without the crazy hackerman stuff is by using google sheets.Keep track in a googlesheets spreadheet, make sure to make it public (share button top right, change button bottom left, make sure it's set on "anyone with a link" , hit done) .Then on your spreadsheet, go to the cell that has your total bankroll, right click it, and "Get link to cell". Now that link should be in your clipboard.In OBS sources ADD > Browser > Name it excel or whatever and hit ok > at URL paste the link you just copied from google sheets and hit ok. Now you have your online excell sheet on your screen.Then just right click that Source that you just created in Sources > click Filters > bottom left click on the + > Crop/Pad > OK > and then just mess around with the Left Top Right Bottom cropping numbers until you have just your cell on screen.Now any time that total gets updated in your sheet it gets updated on your screen too.Yes this is pretty complex too, but if you follow the directions it shouldn't be too hard (assuming that you have OBS studio)LE: you can use this method to have anything on your stream, maybe graphs that update if you'll learn how to do that, for example, without the need to have it open on your other screen and captured, with this method you don't even need the spreadsheet open as it's all online.
Karl Posted September 8, 2020 Author Posted September 8, 2020 I kind of understand you @Stubbe-Unibet, and the problem is that I have not used Excel for years and struggle to remember most of the stuff I knew lolAnyhow here's a screen shot of what I'm up to, and you're right in what you understand what I'm talking about.Just the simplest thing that got me losted.G9 which is latest entry to be updated into I6, and I6 to be updated as more entries are enteredI6 is where I can capture that field to import onto my twitch streamI can't believe that I had to resort to asking for helpGrateful for your help in this problem
Karl Posted September 8, 2020 Author Posted September 8, 2020 Thanks for that @FeelsBadMan Being old school excel, it's easier for me to stick with what I know.....old guy here needs to be talked to like he's a five year oldBut later I will endover to try your method to improve my twitch stream
FeelsBadMan Posted September 8, 2020 Posted September 8, 2020 @Karl google sheets is very very similar to microsoft excel, same formulas, same basic stuff, probably missing some of the very advanced things but I doubt you need those.For having the total in a specific cell, instead of "moving" the last cell , why don't you go to the target cell and type =SUM( and then select the entire profit column without the header, go as far down as you want, that should give you a fixed total cell that updates every time you add a new tournament. So in this case I guess it would be =SUM(F6:F1000) that you would put in the I6 cell
Uni-Rep Posted September 8, 2020 Posted September 8, 2020 @Karl wrote:G9 which is latest entry to be updated into I6, and I6 to be updated as more entries are enteredI6 is where I can capture that field to import onto my twitch streamIn that case, use the offset formula in my previous post. In your case just make it =OFFSET(G6,COUNTA(G:G)-1,0)I might put together a small package tomorrow (I'm off today) with an Excel sheet and the txt file, so all you need to do is download the two files, start adding your own tournament data and pull the txt file into OBS/Streamlabs/xsplit/whatever :) Check the latest poker release notes. Have a look at our poker promotions.
Karl Posted September 8, 2020 Author Posted September 8, 2020 =sum just add everything up in the coloumn and doing a sum to include "F" & "G" coloumns gave different answer....confusing lolwhich is why I wanted to have a formula or something that just gave me the last entry for "G" column, as that column has already been calculated up to the last entry :Smile:For the life of me....it escapes me as to what needed to be in I6 to give me that answerI've worked how to export to text file now, so that's an improvement :Rofl:
Uni-Rep Posted September 8, 2020 Posted September 8, 2020 @Karl just put the previously posted formula in cell i6 :) =OFFSET(G6,COUNTA(G:G)-1,0) Check the latest poker release notes. Have a look at our poker promotions.
Karl Posted September 8, 2020 Author Posted September 8, 2020 oooooohhhh I could kiss you all over if I was gay @Stubbe-Unibet wow...that was too easy, updating every time I enter new entryBig Thanks to you dude
Uni-Rep Posted September 9, 2020 Posted September 9, 2020 @Karl, you're welcome :) As long as you don't change the format of the sheet, it'll indeed update automatically. What the formula does is basically count the number of cells with content in column G. It then uses this number to count down (offset) the number of rows from G6, from which it should return the value :)The OFFSET formula format consists of REFERENCE,ROWS,COLUMNS, and in this case we got G6 as the reference, as this is the cell from which we count down and find the last value. For the rows, we use COUNTA(G:G), to count the number of cells with content. As you got a header in column g, we need to add -1. Columns is irrelevant in this case, as it's all in column G, so we end up with the following formula:=OFFSET(G6,COUNTA(G:G)-1,0).There're a few other ways you could do it. You could for instance use index (combined with count) or a simple lookup.giphy 1 Check the latest poker release notes. Have a look at our poker promotions.
Karl Posted September 13, 2020 Author Posted September 13, 2020 Thanks @Stubbe-Unibet It's starting to come back to me with all this excel malarkey, it's hard to believe that I've got equivalent to Phd in computing......just shows that when you've not done things you've trained to do for a long long time, you forget things I knew what I wanted to do, but my mind was blank.But it has been quite refreshing to know that staff at Unibet are very customer focused and damn right helpful.Once again... a big thankyou for your help 3
Cindy_Essex Posted September 13, 2020 Posted September 13, 2020 I realise that some people/social groups feel the need to say "no homo" but honestly mate, you don't need to say "no homo".
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now