Author: Duncan Roberts
Date: 09:56:42 08/08/04
Go up one level in this thread
To ed
thanks for your help in this. looks good.
duncan
On August 07, 2004 at 20:08:10, Ed Trice wrote:
>There is a way around this. I had to circumvent the "column limit" for a client,
>and the row limit can be handled similarly. In his case, you only go out to
>column "IV", which is 256.
>
>He only needed 100 rows but he required 500 columns, so I create a "virtual
>column extension" using VB logic and multiple sheets, with an indexing
>conversion formular.
>
>If you have only 1 column of data per row, the task of re-using columns to serve
>as virtual rows is easy.
>
>Say you need to lookup something in row 192,347.
>
>Divide by 65536 to get 2.9349822998, add 1, then truncate the decimal.
>
>You have "3" for this result, indicating your number is in the third column.
>
>Where is it exactly?
>
>192347 - (2 * 65536) = 61275.
>
>So, you need to select cell C61275 to get your value.
>
>How do you specify this?
>
>Use the INDIRECT command, something like
>
>=INDIRECT("Sheet1!" & "C" & 61275)
>
>The "!" is the sheet name delimiter.
>
>The "&" concatenates text to build "C61275"
>
>The "Sheet1" is the default name for a sheet, which would need to be changed if
>yours is different.
>
>If you have something more complex in mind, you would need to do something else,
>but indexing is not bounded by 256 columns and 65536 rows if your data is
>relatively rectangular and easy to reference.
>
>>excel only allows you 65,000 or so rows. If you want to change this so that many
>>more are allowed. Is it just a question of changing an int to a long somewhere
>>and how would you do it.? Or is it more complicated than that.?
>>
>>
>>duncan
This page took 0 seconds to execute
Last modified: Thu, 15 Apr 21 08:11:13 -0700
Current Computer Chess Club Forums at Talkchess. This site by Sean Mintz.