Dynamically create array names

[meetarnav]

I play Chess for the Bristol League. Now, the results are reported in a really weird way - see "Individual Results" sheet in the attached workbook.

I want to automatically create array names for each table on this sheet so that I can reference these tables later in formulas. Any help?!

 

[Peter Bartholomew]

Without knowing the sort of calculations you wish to perform, it is difficult to devise a suitable naming scheme. I have suggested a scheme that will return the results for the 6 boards of a match (Divisions 1-3) but, as it is, you would have to define a name for each match number (though could be set up from VBA).

An alternative strategy would be to provide the match number as data and then look up the results by for the match as a range using INDEX. Individual results would obtained by a further application of index to the range.

 

[meetarnav]

Thanks for the reply. Brilliant. I can't seem to understand how some of the names have been defined though? What does #REF mean in the name definition? Also, how did you manage to re-create the tables in the second sheet
just by referring to two arrays. I can't replicate the effect!

 

[Peter Bartholomew]

#REF! simply means I was careless and forgot to delete some names when I deleted the columns that contained the cells. The 'match' and 'board' cells had originally contained values which would allow the user to request the result of any particular match.

What I then changed to was the idea of bringing back all the data associated with any given match by its name. This still involves more names than I would really like to see.

The formula returns the match scores by intersecting ranges comprising complete worksheet columns ('Division1', 'Division2', 'Division3'), with a further set of ranges that are comprised of rows selected using the INDEX formula for each of the board 1 and board 6 games. The ":" operator in the range definitions defines a range by referencing the first and last cells of the range.

The formula {= Division1 Match1} returns 48 cells at a time and so must be committed as an array using Ctrl/Shift/Enter. Having the two ranges in a formula with a space between them returns the intersection of the two, here vertical and horizontal ranges.

It still might be a good idea to use an user-provided value 'matchNumber' to request the result of a given match by index e.g. 'selectedMatch' could refer to
= INDEX( Results, (10*(matchNumber - 1) + 6) + 1, 0 )
: INDEX( Results, (10*(matchNumber - 1) + 6) + 6, 0 )

Division 4 results would require 8, rather than 10, rows per match and should return rows 1-4 rather than 1-6.

What is best very much depends upon the analysis you wish to carry out.

 

[meetarnav]

Thanks for the reply. I will check it out. What I also found out was that there is an automatic naming function in excel wherein Excel creates named arrays on it's own. I think I can use that too but the results will need some processing into a particular format.