Using Excel Options

[Guest]

Upon opening a particular Excel file, when I try to use the Options tab, I get a response; "The number must be between 1 and 32767. Try again by entering a number in this range.

What is causing this response, and how do I eliminate it?

 

[Frank Kabel]

Hi Jeff
are you openening a Text file?

 

[Dave Peterson]

Can you break your text file into smaller pieces and import each piece
separately.

Or you could use a macro that imports each line:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596
XL: Importing Text Files Larger Than 16384 Rows
(written for xl95, but has a note to change stuff for xl97+)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Hi Fran
No, it's not a text file; it's an Excel spreadsheet linked to vlookup files in another folders; some of the folders are data and some are customer info (text). When I open Tools/Options and click any of the folders in Options, it automatically returns the popup "The number must be between 1 and 32767. Try again by entering a number in this range."

Essentially, all I wanted to do was eliminate the gridlines and zero values on the primary spreadsheet, and at this point, I'm stumped

Regards
Jeff Roger

----- Frank Kabel wrote: ----

Hi Jef
are you openening a Text file

--
Regard
Frank Kabe
Frankfurt, German


Jeff rogers wrote

 

[Guest]

Hi Dave

It’s an Excel spreadsheet linked to vlookup information on a Pivot table, which is 25000 rows in another folder, and also vlookup to two other folders of data and customer info (text). I’m just learning Pivot tables, so I don’t know how to reduce the information so it reduces the number of rows. And I don’t know that importing the data to another folder will solve my problems.

Essentially, all I wanted to do was eliminate the gridlines and zero values on the primary spreadsheet, and at this point, I'm stumped

Regards
Jeff Roger


----- Dave Peterson wrote: ----

Can you break your text file into smaller pieces and import each piec
separately

Or you could use a macro that imports each line

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q12059
XL: Importing Text Files Larger Than 16384 Row
(written for xl95, but has a note to change stuff for xl97+

If you're new to macros, you may want to read David McRitchie's intro at
http://www.mvps.org/dmcritchie/excel/getstarted.ht


Jeff rogers wrote

 

[Guest]

Frank

Forgot to mention - The primary spreadsheet is also linked to vlookup information on a Pivot table, which is 25000 rows in another folder - probably causing the problem. I’m just learning Pivot tables, so I don’t know how to reduce the information so it reduces the number of rows.

Regards
Jeff Roger


----- Frank Kabel wrote: ----

Hi Jef
are you openening a Text file

--
Regard
Frank Kabe
Frankfurt, German


Jeff rogers wrote

 

[Dave Peterson]

About the only spot that looks like it accepts numbers that big is on the
Tools|options|calculation tab (iteration box).

You could try this to see if it helps:

Open the workbook (don't touch tools|options).

Instead hit alt-f11 (to get to the VBE)
then hit ctrl-g to see the immediate window

Type this in and hit enter:
application.maxiterations = 100

Then back to excel and tools|options and see what happens.

===
If it didn't help, maybe reregistering excel will help:

close excel
windows start button|Run
excel /unregserver
followed by
excel /regserver

It resets excel's registry entries to factory defaults.