8192 character count limit wasn't there until my files were moved to Onedrive from a Shared Drive

[SandsB]

Sort of.... We have a LOT of reports and many of them have a lot of formulas with more than 8192 characters. They've worked for many years. When all of our files migrated from shared drives to Onedrive in May 2023, the long formulas worked fine. Now, I'm copying the 2023 reports (on OneDrive) to a new 2024 folder (on the same OneDrive) and updating the links to point to 2024 folders and 2024 file names. BUT - holy crap - the formulas are truncated to 8192 characters. I didn't even know that was a limit. The 2023 and 2024 folders are under a root that has folders for 2022, 2021, 2020, etc. All with the long formulas that all work. I'm thinking that when our IT people migrated us to OneDrive they did it in a way that retained to longer formulas and whatever that was doesn't work when I copy my 2023 folder and rename it to 2024. It'll take weeks (at least) to get an answer from IT.

I've used these files for years and yes I know I could split up formulas into smaller formulas in multiple cells. That's not what I'm looking for. There are too many of these links to mess with. Literally thousands. And I need my reports as soon as possible.

Any ideas about what I can do?

 

[KRice]

I've dealt with some long and messy formulas, but really cannot imagine a practical one that exceeds Excel's 8192 character limit. Do you have file path information in those formulas? It may be that migrating from one drive to another changed path references (and added extra directory navigation layers to the path). Do you know which formulas are causing the problem? This might help with at least diagnosing where the problems are occurring:

Formula longer than 8192 characters. Identify cell?

Receive an error: "One or more formulas in this workbook are longer than allowed limit of 8192 characters. To avoid this limitation, save the workbook in excel binary workbook format." Workbook contains thousands of used cells. How can I identify the cell that is longer than 8192 characters...

www.mrexcel.com

 

[SandsB]

I know they can be more efficient - I'm just trying to get them work as originally written ASAP.

 

[KRice]

You’re probably correct about efficiency, but I didn’t say anything about efficiency. I included the URL to show how some code can be used to identify which cells are causing the problem. Maybe you know this already, and the code is not necessary, but the first step would be to identify the problematic cells and then evaluate them to determine why they are not working. As I mentioned, it may be a file path length issue if you have embedded paths in the formulas. If that is the issue, then you might be able to explore some options for either moving the files to a shorter path location or define a shortcut name that maps to the current location…but I’m just guessing without more information.

 

[SandsB]

When I copied the files, the formulas were truncated at 8192 characters.

 

[KRice]

Do you have a pre-migration version of the file backed up somewhere that does work? Perhaps a bonafide backup, or a version that was emailed, etc...anything that worked before?

 

[SandsB]

I have several years with the complete links. I copied the 2023 file and changed the date in the filename from 2023 to 2024. When I went to change the links to the 2024 input files that's when I saw the links had been truncated. So when sys admins migrated things the links were all there; when I copy the files and do nothing else the links extend to 8192 characters and then just get cut off at that point.