Warning when trying to save workbook as xlsb with reference to external named range (Mac)

**seh0872** · 12-28-2023, 03:22 PM

Two files, both .xlsb - File B looks to File A. File A has 15 columns and 270,000 rows of data.

If the VLOOKUP in File B refers to the full unnamed range in File A, everything OK. File B saves as an .xlsb with no issues.

If the VLOOKUP in File B refers to a partial unnamed range in File A (say 15 columns and 15 rows), everything OK. File B saves as an .xlsb with no issues.

If the VLOOKUP in File B refers to a partial NAMED range in File A (say 15 columns and 15 rows titled "SHORT"), everything OK. File B saves as an .xlsb with no issues.

BUT...

If the VLOOKUP in File B refers to the full NAMED range in file A (15 columns and 270,000 rows titled "LONG"), file B will not save as an .xlsb, saying that "Some features cannot be saved".

Has anyone encountered this? Try on your version. I'm baffled as I can find no information that .xlsb is limited to a set number of rows, and it works just fine if the range is unnamed. As soon as you name it, it doesn't work. But, as I stated, this only seems to apply to large number of rows. If small numbers of rows, named ranges are OK.

????

**6StringJazzer** · 12-28-2023, 07:10 PM

To try using my version, it would be helpful to have your file as a test case. Can you attach it? See yellow banner at the top of the page.

**seh0872** · 12-28-2023, 09:06 PM

The files are too large to upload (in excess of forum limits).

Decreasing the amount of rows / data in the files to fit under the limits makes the "Save as .xlsb" problem go away. The problem is limited to a named range with hundreds of thousands of rows of data. Unfortunately, you'd have to recreate it locally.

File A: Add 10 or 15 columns and at least 250,000 rows. Data can be duplicated, it shouldn't matter. Name just rows 1-15 as a named range.

File B: Created a VLOOKUP to look up data in column A and retrieve something else from the table. Use the named range in the formula. Save as .xlsb. Should work.

Reopen file, change formula to do a VLOOKUP for the entire table (all 250,000 rows) -- don't name the range in file A!. Save File B as .xlsb. Should work.

Reopen files -- name the full table a named range in File A. Change the VLOOKUP in file B to use the named range instead of the cell references. Save as .xlsb. Watch warning appear.

I could try to compress the files and attach. Maybe that would help. But the actual data isn't relevent, just the amount of it.

**6StringJazzer** · 12-28-2023, 10:29 PM

The limit for a .xlsx file is 1MB. However, for some obscure reason, a zip file is allowed to be up to 9.77MB.

Meantime, I'll see what I can do with your description.

Warning when trying to save workbook as xlsb with reference to external named range (Mac)

LinkBack

Thread Tools

Rate This Thread

Display

Warning when trying to save workbook as xlsb with reference to external named range (Mac)

Re: Warning when trying to save workbook as xlsb with reference to external named range (M

Re: Warning when trying to save workbook as xlsb with reference to external named range (M

Re: Warning when trying to save workbook as xlsb with reference to external named range (M

Thread Information

Users Browsing this Thread

Similar Threads

Excel Binary File Size Issue

Problem with saving a workbook as a binary file (.xlsx -->>> .xlsb)

Importing a Binary File, displaying as Hex, and saving the file as a Binary file again

[SOLVED] Save the split the workbook file type as Excel Binary Workbook From Run Macro

Referencing other workbook file names by formula

Binary workbook .xlsb data import issue

Macro to save workbook by referencing cell in another workbook for file name and file path

Bookmarks

Bookmarks

Posting Permissions