Expanding the sheet

[Student]

Excel's single sheet is a 65,536x256 table. Please, can the latter
number be expanded?

Thank you very much.

Student

 

[Chip Pearson]

No, you cannot increase the number of columns. It is fixed at
256.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Student]

No, you cannot increase the number of columns. It is fixed at
256.

Please, I am right to assume also that:

- the number of rows cannot be increased beyond 65,635,

- the number of interations cannot be increased beyond 10,000?

Thank you very much for your time.

Student

 

[Dave Peterson]

Yep for the rows.

But in xl2002, I could increase the number of iterations to 32767.

 

[Student]

Yep for the rows.

I see. Thank you very much. Please, how do I do it? What is the maximum of rows?

But in xl2002, I could increase the number of iterations to 32767.

Right. I finally managed to get to 32767.
What do you mean by x12002, please?

Thank you for your time.

Student

 

[Dave Peterson]

You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)

xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.

 

[Dave Peterson]

I didn't HAVE other versions to test....

You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)

xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.

 

[Student]

You assumed that: "the number of rows cannot be increased beyond 65,635"
(I was confirming that--not offering a workaround to increase it--you can't in
excel)

Yes. I see. You are absolutely right. My mistake, I am sorry.

xl2002 is part of OfficeXP. I used that for my testing of the iterations. I
didn't other versions to test, so I couldn't state with certainty that they also
went up to 32767.

I see. Thank you.


Thank you very much for writing.

Student
P.S. Perhaps, if I am already not a nuisance to this newsgroup (I
mean, Excel is so great I just can't believe my eyes sometimes - to
say only that it's functionality is Turing complete, I am building a
complete 8088 circuitry in it(!). Please, what is the greatest number
of sheets in a single file? Is it 256?

 

[Dave Peterson]

Not a nuisance at all. (Sorry to confuse you before <g>.)

But the number of sheets in a workbook is limited by available memory. You can
add them until you can't add any more.

From a usability standpoint, I wouldn't put too many in one workbook, though.
It becomes hard to navigate and if you have lots of data, it becomes bulkier and
slower to do things.

 

[Student]

Not a nuisance at all. (Sorry to confuse you before <g>.)

You are very very kind.

But the number of sheets in a workbook is limited by available memory.
You can add them until you can't add any more.

That's a relief. Thank you very much.

From a usability standpoint, I wouldn't put too many in one workbook, though.
It becomes hard to navigate and if you have lots of data, it becomes bulkier
and slower to do things.

I see. Thank you.

OK. Perhaps, if I am not just pushing my luck with your kindness and
helpfulness, please, would you care to tell me how to create a button
which, when pushed, would invert the contents of a particular cell
associated with it(i.e. it would check the contents of a cell for a 0
or a 1, and rewrite it respectively with a 1 and a 0? I am afraid I
don't have the slightest idea of how to even start.

Thank you very much for your time.

Student

 

[J.E. McGimpsey]

One way:

Public Sub InvertCell()
With ActiveCell 'Or, e.g.: With Range("A1")
If IsNumeric(.Value) Then _
If .Value = 0 Or .Value = 1 Then _
.Value = 1 - .Value
End With
End Sub

Put this in a regular code module and attach it to a button. If
you're not familiar with macros, you may want to look at David
McRitchied's site:

Getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

J.E. gave you a nice function that you could use, but you could also use a
helper column next to it and just put a formula like:

=1-A1

And drag down. 1's will be 0's and 0's will become 1's.
(but bad things will happen to values that aren't 1 or 0.)

Depending on what you're doing, this might be a way to keep the original value,
so you don't lose track of what was there.

(If you really want to just click a button and have it do the work, then J.E.'s
method is the way to go.)

And just a little additional info for J.E.'s method.

View|Toolbars|and select Forms
click on the button icon and draw it on the sheet.
When you let go, you can assign J.E.'s macro to it.
(If you don't do it now, you can right click on the button and assign it later.)

And I'd put it in Row 1 (and make row 1 a little taller).

Then click on B1 and Window|Freeze Panes.

Then row 1 (and the button is always visible).

 

[Student]

One way:

Public Sub InvertCell()
With ActiveCell 'Or, e.g.: With Range("A1")
If IsNumeric(.Value) Then _
If .Value = 0 Or .Value = 1 Then _
.Value = 1 - .Value
End With
End Sub

Thank you for your interest. I will do my best to implement this code
(I am afraid I do not have much experience in this regard).

Put this in a regular code module and attach it to a button. If
you're not familiar with macros, you may want to look at David
McRitchied's site:

Getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

So thank you very much for the link. This is really very kind of you.

Best regards,
Student

 

[Student]

J.E. gave you a nice function that you could use, but you could also use a
helper column next to it and just put a formula like:

=1-A1

And drag down. 1's will be 0's and 0's will become 1's.
(but bad things will happen to values that aren't 1 or 0.)
Right.

Depending on what you're doing, this might be a way to keep the original value,
so you don't lose track of what was there.

(If you really want to just click a button and have it do the work, then J.E.'s
method is the way to go.)

Yes. Right.

And just a little additional info for J.E.'s method.

View|Toolbars|and select Forms
click on the button icon and draw it on the sheet.
When you let go, you can assign J.E.'s macro to it.
(If you don't do it now, you can right click on the button and assign it later.)

And I'd put it in Row 1 (and make row 1 a little taller).

Then click on B1 and Window|Freeze Panes.

Then row 1 (and the button is always visible).

Thank you very much indeed. I will try to do it this way.

Thank you very much for writing.

Student