OOTP Developments Forums - View Single Post

NoOne · 05-23-2017, 12:55 PM

Tip: if you do something with stats or anything you regularly export from the game to use...

SS are made to automate mostly everything except inputs... if the input comes from an exported file from OotP, everything should be automated in nature.

so, you create an external data link to the exported file -- which resides in the same directory with the same name each time you export from that league -- any time the spread sheet loads or you refresh the external links, all inputs are updated immediately.

Now, you have this exported data residing in a worksheet -- unadalterated, and it must stay that way... So, if you can't work on it directly, you need to link to the individual fields on a different worksheet (name them as you wish to keep track).

need the whole thing copied? even that is simple to do:

this involves doing 1 row or column then highlight and fill the rest -- sometimes 2 rows/columns if you are manipulating the data in some cases.. to form a 'pattern' for the fill funciton to repeat (maybe changing horizontal orientation of the data to a vertical one).

so you do "=!worksheet3.A1" in the first field you want the data to be worked on to be seen... you learn to use b2, but a1 is fine, too. then simply fill accross or down to create the first row/column. it will automatically change variables (A1 becomes b1, c1, etc... if you fill left to right from a1. )

Now that the first row or column is done, you repeat that fill process for the rest of the table.

adding a $ will lock row or column e.g. $a$1 will never change as you fill a row or column. $a1 will lock the column. a$1 locks the row. can use it for worksheet names too.

obviously you won't be doing all of it... but you link to data to work on it, but never actually change the fields on that exported data sheet, except with a newly exported file / updated data link.

i have an ltm spreadsheet... it adds up the subleagues from the exported data during this process. so it looks somethign like "=sum(!batting.C1:C2)" .. i needed an empty row so that i could "fill" easily... then i sum/average/divide etc on that sheet.

because all formulas involve variables that lead to that exported data, everything updates when you update the data links. always use variables in the formula, even if you have to make some "$a$1" constant field instead of literally typing it into the each formula. now if that supposed constant changes, you don't have to re-do the entire sheet.

all inputs should be linked to in this way... so, after you setup a spreadsheet properly, all you ahve to do is update data links and/or change a few input fields and the entire thing should change for you.

i have a recent post explaining how i did my LTM spreadsheet somewhere, welcome to search for it. think it was in "new to the game" forum.

05-23-2017, 12:55 PM	#7
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 0/1 (3)	Tip: if you do something with stats or anything you regularly export from the game to use... SS are made to automate mostly everything except inputs... if the input comes from an exported file from OotP, everything should be automated in nature. so, you create an external data link to the exported file -- which resides in the same directory with the same name each time you export from that league -- any time the spread sheet loads or you refresh the external links, all inputs are updated immediately. Now, you have this exported data residing in a worksheet -- unadalterated, and it must stay that way... So, if you can't work on it directly, you need to link to the individual fields on a different worksheet (name them as you wish to keep track). need the whole thing copied? even that is simple to do: this involves doing 1 row or column then highlight and fill the rest -- sometimes 2 rows/columns if you are manipulating the data in some cases.. to form a 'pattern' for the fill funciton to repeat (maybe changing horizontal orientation of the data to a vertical one). so you do "=!worksheet3.A1" in the first field you want the data to be worked on to be seen... you learn to use b2, but a1 is fine, too. then simply fill accross or down to create the first row/column. it will automatically change variables (A1 becomes b1, c1, etc... if you fill left to right from a1. ) Now that the first row or column is done, you repeat that fill process for the rest of the table. adding a $ will lock row or column e.g. $a$1 will never change as you fill a row or column. $a1 will lock the column. a$1 locks the row. can use it for worksheet names too. obviously you won't be doing all of it... but you link to data to work on it, but never actually change the fields on that exported data sheet, except with a newly exported file / updated data link. i have an ltm spreadsheet... it adds up the subleagues from the exported data during this process. so it looks somethign like "=sum(!batting.C1:C2)" .. i needed an empty row so that i could "fill" easily... then i sum/average/divide etc on that sheet. because all formulas involve variables that lead to that exported data, everything updates when you update the data links. always use variables in the formula, even if you have to make some "$a$1" constant field instead of literally typing it into the each formula. now if that supposed constant changes, you don't have to re-do the entire sheet. all inputs should be linked to in this way... so, after you setup a spreadsheet properly, all you ahve to do is update data links and/or change a few input fields and the entire thing should change for you. i have a recent post explaining how i did my LTM spreadsheet somewhere, welcome to search for it. think it was in "new to the game" forum.