How to fake/simulate more than 256 columns

[tbd]

Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used
by another application. So, the spreadsheet is a "UI" where users' control
over sheet is limited.

The data represents a "daily schedule" where the user contols how many
days are defined. The right-most column represents the last day defined by
the schedule. It's reasonable to expect a schedule will (need to) define
more than 256 days (columns).

I'm considering maintaining a data-set on another sheet, or in a
disk-file, and just populate the UI according to what user wants to see. For
instance, maybe up to 60 columns are used, and when the user wants to see a
61st day, the data is programmatically "scrolled" to the left such that
column 60 holds day 61, and column 1 holds day 2. Of course it would be nice
to employ the standard worksheet scroll-bars, but if I have to manage one, so
be it.

If anybody [is not completely confused by now, and] has tried anything
like this, or if you have any enlightening ideas, I'd be interested in
hearing from you.

Thanks/Cheers!

 

[Dave Peterson]

Can you transpose your rows and columns? If you're not showing it to the user,
your code could just do things "backwards" <vbg>.

Can you upgrade to xl2007 (with 1 million rows and 16k columns)?

If no to both, then maybe you could reserve two columns to act as a key.

The first column is the key for that record. The second column is just a
counter of which row you're on for that record. You'll have to map your fields
(column E of Row 2 for employee 12345 record).

But that's pretty much as multiple sheets.

You may to consider using a userform to show the info/get input from the user.

You could look at John Walkenbach's enhanced data form:
http://j-walk.com/ss/dataform/index.htm

The source code is available for a small fee ($20 USA, IIRC). So you can modify
it as much as you want.

Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used
by another application. So, the spreadsheet is a "UI" where users' control
over sheet is limited.

The data represents a "daily schedule" where the user contols how many
days are defined. The right-most column represents the last day defined by
the schedule. It's reasonable to expect a schedule will (need to) define
more than 256 days (columns).

I'm considering maintaining a data-set on another sheet, or in a
disk-file, and just populate the UI according to what user wants to see. For
instance, maybe up to 60 columns are used, and when the user wants to see a
61st day, the data is programmatically "scrolled" to the left such that
column 60 holds day 61, and column 1 holds day 2. Of course it would be nice
to employ the standard worksheet scroll-bars, but if I have to manage one, so
be it.

If anybody [is not completely confused by now, and] has tried anything
like this, or if you have any enlightening ideas, I'd be interested in
hearing from you.

Thanks/Cheers!

 

[tbd]

Thanks Dave!
I didn't know about Excel 2007 supporting 16k columns. I like this
solution the best, on the other hand, I'm outta work and this gig isn't
paying anything. Still, will check with "customer" to see what they're using.

I've never used Transpose, but it looks very promising. I still need
to talk myself into implementing the scrolling controls and data
synchronization logic - but this is definately doable.

I'm not sure, but I think the "keyed" approach, like transpose, also
utilizes data storage on another sheet, with the keys mapping that data to
cells in the UI. I hope transpose works so I can copy en mass!

Excellent options, thanks!

Can you transpose your rows and columns? If you're not showing it to the user,
your code could just do things "backwards" <vbg>.

Can you upgrade to xl2007 (with 1 million rows and 16k columns)?

If no to both, then maybe you could reserve two columns to act as a key.

The first column is the key for that record. The second column is just a
counter of which row you're on for that record. You'll have to map your fields
(column E of Row 2 for employee 12345 record).

But that's pretty much as multiple sheets.

You may to consider using a userform to show the info/get input from the user.

You could look at John Walkenbach's enhanced data form:
http://j-walk.com/ss/dataform/index.htm

The source code is available for a small fee ($20 USA, IIRC). So you can modify
it as much as you want.

Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used
by another application. So, the spreadsheet is a "UI" where users' control
over sheet is limited.

The data represents a "daily schedule" where the user contols how many
days are defined. The right-most column represents the last day defined by
the schedule. It's reasonable to expect a schedule will (need to) define
more than 256 days (columns).

I'm considering maintaining a data-set on another sheet, or in a
disk-file, and just populate the UI according to what user wants to see. For
instance, maybe up to 60 columns are used, and when the user wants to see a
61st day, the data is programmatically "scrolled" to the left such that
column 60 holds day 61, and column 1 holds day 2. Of course it would be nice
to employ the standard worksheet scroll-bars, but if I have to manage one, so
be it.

If anybody [is not completely confused by now, and] has tried anything
like this, or if you have any enlightening ideas, I'd be interested in
hearing from you.

Thanks/Cheers!

 

[Dave Peterson]

I didn't mean that you could transpose the data in code or by a formula. You're
still limited by the number of cells in the row.

(But if that works for you, I'm taking credit <vbg>.)

I really meant that you'd enter your data in columns instead of rows.

If you had an "input" sheet, your code could do the translation of rows to
columns, though.

And take a look at John's Enhanced data form. It could look very nice and
professional.

If you've never created a userform...

Debra Dalgleish has some nice notes about userforms:
http://contextures.com/xlUserForm01.html
(video: http://contextures.com/xlVideos05.html#UserForm01)
and
http://contextures.com/xlUserForm02.html

Thanks Dave!
I didn't know about Excel 2007 supporting 16k columns. I like this
solution the best, on the other hand, I'm outta work and this gig isn't
paying anything. Still, will check with "customer" to see what they're using.

I've never used Transpose, but it looks very promising. I still need
to talk myself into implementing the scrolling controls and data
synchronization logic - but this is definately doable.

I'm not sure, but I think the "keyed" approach, like transpose, also
utilizes data storage on another sheet, with the keys mapping that data to
cells in the UI. I hope transpose works so I can copy en mass!

Excellent options, thanks!

Can you transpose your rows and columns? If you're not showing it to the user,
your code could just do things "backwards" <vbg>.

Can you upgrade to xl2007 (with 1 million rows and 16k columns)?

If no to both, then maybe you could reserve two columns to act as a key.

The first column is the key for that record. The second column is just a
counter of which row you're on for that record. You'll have to map your fields
(column E of Row 2 for employee 12345 record).

But that's pretty much as multiple sheets.

You may to consider using a userform to show the info/get input from the user.

You could look at John Walkenbach's enhanced data form:
http://j-walk.com/ss/dataform/index.htm

The source code is available for a small fee ($20 USA, IIRC). So you can modify
it as much as you want.

Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used
by another application. So, the spreadsheet is a "UI" where users' control
over sheet is limited.

The data represents a "daily schedule" where the user contols how many
days are defined. The right-most column represents the last day defined by
the schedule. It's reasonable to expect a schedule will (need to) define
more than 256 days (columns).

I'm considering maintaining a data-set on another sheet, or in a
disk-file, and just populate the UI according to what user wants to see. For
instance, maybe up to 60 columns are used, and when the user wants to see a
61st day, the data is programmatically "scrolled" to the left such that
column 60 holds day 61, and column 1 holds day 2. Of course it would be nice
to employ the standard worksheet scroll-bars, but if I have to manage one, so
be it.

If anybody [is not completely confused by now, and] has tried anything
like this, or if you have any enlightening ideas, I'd be interested in
hearing from you.

Thanks/Cheers!

 

[tbd]

Hi Dave,
Thanks for the links below. I may explore the user-forms link,
though, hope to avoid complexity. (Probably just fear of having to learn
something new.)

Thanks/Cheers!

I didn't mean that you could transpose the data in code or by a formula. You're
still limited by the number of cells in the row.

(But if that works for you, I'm taking credit <vbg>.)

I really meant that you'd enter your data in columns instead of rows.

If you had an "input" sheet, your code could do the translation of rows to
columns, though.

And take a look at John's Enhanced data form. It could look very nice and
professional.

If you've never created a userform...

Debra Dalgleish has some nice notes about userforms:
http://contextures.com/xlUserForm01.html
(video: http://contextures.com/xlVideos05.html#UserForm01)
and
http://contextures.com/xlUserForm02.html

Thanks Dave!
I didn't know about Excel 2007 supporting 16k columns. I like this
solution the best, on the other hand, I'm outta work and this gig isn't
paying anything. Still, will check with "customer" to see what they're using.

I've never used Transpose, but it looks very promising. I still need
to talk myself into implementing the scrolling controls and data
synchronization logic - but this is definately doable.

I'm not sure, but I think the "keyed" approach, like transpose, also
utilizes data storage on another sheet, with the keys mapping that data to
cells in the UI. I hope transpose works so I can copy en mass!

Excellent options, thanks!

Can you transpose your rows and columns? If you're not showing it to the user,
your code could just do things "backwards" <vbg>.

Can you upgrade to xl2007 (with 1 million rows and 16k columns)?

If no to both, then maybe you could reserve two columns to act as a key.

The first column is the key for that record. The second column is just a
counter of which row you're on for that record. You'll have to map your fields
(column E of Row 2 for employee 12345 record).

But that's pretty much as multiple sheets.

You may to consider using a userform to show the info/get input from the user.

You could look at John Walkenbach's enhanced data form:
http://j-walk.com/ss/dataform/index.htm

The source code is available for a small fee ($20 USA, IIRC). So you can modify
it as much as you want.

tbd wrote:

Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used
by another application. So, the spreadsheet is a "UI" where users' control
over sheet is limited.

The data represents a "daily schedule" where the user contols how many
days are defined. The right-most column represents the last day defined by
the schedule. It's reasonable to expect a schedule will (need to) define
more than 256 days (columns).

I'm considering maintaining a data-set on another sheet, or in a
disk-file, and just populate the UI according to what user wants to see. For
instance, maybe up to 60 columns are used, and when the user wants to see a
61st day, the data is programmatically "scrolled" to the left such that
column 60 holds day 61, and column 1 holds day 2. Of course it would be nice
to employ the standard worksheet scroll-bars, but if I have to manage one, so
be it.

If anybody [is not completely confused by now, and] has tried anything
like this, or if you have any enlightening ideas, I'd be interested in
hearing from you.

Thanks/Cheers!