| View previous topic :: View next topic |
| Author |
Message |
pmcnamara
Newbie
Joined: 25 Jun 2012
Posts: 2
|
 Posted: Mon Jun 25, 2012 11:22 am Post subject: Copying Table Arrary/Lookup Tables ? |
 |
|
There might be a better way to do what I'm describing below, If you have a better way, please let me know. This is the best way I know how to do this, but I'd gladly use a better way if a better way exists.
I have a Calc spreadsheet which I use to track and score the performance of our race cars each week.
I have a copy of the spreadsheet here : http://www.springportmotorspeedway.com/Race_Stats.ods if you'd like to download it and see it.
What I do each week is copy the "BLK" tab (which is my short hand for blank), which is the template for each week's tab. I rename the copied tab to the new week (next week will be WK8).
That is all easy. the problem is, each TAB has 25 lookup tables on it.
Changing the formulas is easy.. I just do a "find/replace" and change WK1 to WKx and it updates all the vlookups to the new array it is looking for. (x=current week)
BUT -- Now I have to re-create 25 new lookup tables.
There must be an easier way to create these tables (which are identical to the week previous, just on a new tab).
Any thoughts or ideas or ways to do this, other then manually re-creating all 25 arrays (the process I'm doing now weekly). |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Tue Jun 26, 2012 4:29 am Post subject: |
|
|
I would suggest getting rid of the spreadsheet altogether and moving to a data base with a single set of data tables and a set of reports to do what you want.
I expect that this is not what you want to hear but, really, a spreadsheet is just not the right tool. It works but it just is suboptimal at best and error prone to boot. Also seeSpreadsheet Addiction
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
pmcnamara
Newbie
Joined: 25 Jun 2012
Posts: 2
|
| Posted: Wed Jun 27, 2012 4:25 am Post subject: |
|
|
I think a database would probably work better, I run into the problem of not really understanding databases and I'm uncomfortable dumping the spreadsheet till I have a working db.
I was a functional access user 10 years ago.. but I haven't opened or touched it in 10 years and have never used openoffice's db application. |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Wed Jun 27, 2012 4:47 am Post subject: |
|
|
| pmcnamara wrote: | I think a database would probably work better, I run into the problem of not really understanding databases and I'm uncomfortable dumping the spreadsheet till I have a working db.
I was a functional access user 10 years ago.. but I haven't opened or touched it in 10 years and have never used openoffice's db application. |
I agree totally. I know almost nothing about data bases myself. I tend to use a stats package for most of my data manipulation and am just slowly trying to teach myself something about data bases.
I think my point is that it does not look easy to do what you want in a spreadsheet. Heck, I'd never even consider trying it and it might be faster in the long run to bite the bullet and go for a data base.
Or for that matter, given what you are doing, it may be just as easy to do in R as in Calc. Again the learning curve is a bit nasty but it probably would be faster and safer in terms of data integrity in the long run.
I don't use complicated spreadsheets so I am having a problem seeing exactly what you are doing but it looks like you have a couple of basic tables for drivers ( and cars?) and much of the rest is calculations based on weekly results. Is this correct?
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
|
