Over the years I have developed a set of Excel workbooks and sheets. Due to my lack of knowledge, many of the formulas are not optimal. In one instance, I am using historial results stored on a separate sheet and to access the values associated with averages of that data, Iam using a formulas and macros that have to reference only I cell as I dont know how to write a reference to analyse data in 2 cells.
For example, my historical data sheet stores the results of races. One column of that sheet will contain data for a race of a particular distance, at a particular track. I then use another column to store data for a different distance at the same track and so on. This is OK, I use data such as Mel3 to define a track Mel(Melbourne) and a distance 3(a distance between 300 and 399 metres). This is fine in many cases, but often there are tracks where races are run over 2 distances, for example, 310m and 360m. If I were to just use Mel3, I would be getting data from both. What I currently do is change the reference (manually) so that the 310m might be describad as Mel2 and the 360m as Mel 3 and would keep the historiccal data in 2 separate columns. An example of this formula would be HLOOKUP(G51421,RECORDS,2,FALSE). Using HLOOKUP, G51421 uses the reference to the MEL3 designation of the current race and the "Records" name refers to a set of names, which would include Mel 2, Mel3, Mel4, etc.
What I would like to do is store the data as it really is. Distance column would show 310 or 360 (as in the example above, and as it is currently) but the track would simply be MEL. Then when I try and run any formula, I would need to define the data by 2 parameters, for example, 360m and MEL, not as currently Mel3. I hope that someone can understand my explanation.
Thanks
Bookmarks