• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error Report when adding rows into table

C

charlesbaxter

New Member
All,


I have created a table with 31 columns, only one row inputted.


There are a number of lists, one cell of conditional formatting but that is about it.


When a new row is added and I try save the Excel, the following warning comes up – 'One or more formulas in this workbook are longer than the allowed limit of 8,192 characters. To avoid this limitation, save the workbook in the Excel 2007 Binary Workbook format.'


When I try and add a row in the table in the Binary Workbook format, the Error Report appears and the workbook shuts down.


How have I exceeded the 8,192 characters as there is nothing overly complex in the table? It surprises me I cannot add any more rows without an error format coming up? Is there some way I can condense the information as I will be adding 100’s of rows in future years.


Thank you in advance for any help you can provide!
 
Hi, charlesbaxter!

Give a look at this: it's the second sticky post in the main page at this forums:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
Hi, charlesbaxter!


In my 2010 version it doesn't pop up that message when trying to save... it just crashes Excel , displays "Windows is looking for a solution for the problem...", hahaha... And restarts later with the row added. For additional rows I don't get the problem, neither inserting before any recently added row nor the original one (last).

I'm trying to find out why, but I'm as clueless as Osama (not Obama... or Obama too?) on August 1st Sunday in NY.


'til now I found 3 named ranges with wrong reference: SubsetList, TPAList and TTAList. I deleted them but I bet it isn't related.

I'm looking towards Data Validation options, used in the drop down list boxes... Let' see.


Gotcha! But I don't know why yet... but's the beginning, isn't it?


I did this:

- created a new worksheet

- selected range AG1:AZ50 from original sheet (any that includes all the named ranges)

- cut selection

- pasted selection in AG1 of new sheet (so the named ranges pointed there)

- insert a row in original sheet, positioning on row 2

... et voila! It worked fine.


So I suppose that Data Validation didn't like at all that its references change, causing random errors to be raised: 8192 length in your 2007 and crashing nearly like BSOD in my 2010.


Here you have the modified file:

http://www.2shared.com/file/_IzXDkeN/Engagement_tracker_2012_-_Erro.html


I don't know if its suitable for you to split sheets, but for the moment it's better than nothing. Let me know how you go on with this issue, if you find a smoother solution.


Regards!
 
Hi, charlesbaxter!


After posting before, I decided to try copying and inserting the whole row 2 before itself, so as to maintain data validations, and I got this error message:

"Esta operación no está permitida: está intentando desplazar celdas en una tabla de la hoja de cálculo" -----> in english: "This operation is forbidden: you're trying to move cells in a table of the worksheet" (or something alike, my Excel it's in spanish).


So I believe I was right towards the solution, Data Validation didn't like mobile references.


The final check was:

- select A2:AE2 (whole table row 2, not whole sheet row 2)

- copy & insert, or just insert

... and no problem arised!


Regards!
 
Thanks, I have tried this again but it doesnt seem to be working - could you please explain step by step the changes I need to make to the Excel sheet?


This would be a great help, thanks.
 
Hi, charlesbaxter!


I try to write down more clearly.

The path I lead towards finding the solution is explained in previous posts.


This is step-by-step how did I implemented it:


1) Moving named ranges data


a) created a new worksheet: right button over the sheet name tab at the bottom of the window

b) selection tables definition ranges: in the original sheet (2012), selected range AG1:AZ50, because NatureofworkList goes up to column AO and Engagement up to row 21, so AG1:AZ50 includes them all

c) cutting the selection: Ctrl-X

d) positioning in target cell of new sheet: I choosed AG1 so as to keep the data in the same relative place, but I'd have chosen any cell

d) pasting the cut selection: Ctrl-V


2) Checking and testing

a) in original worksheet, positioned on row 2, right button, insert, ... and ok

b) Ctrl-Z (undo, for checking all cases with original scenario)

c) idem a), but select whole row 2, insert copied cells, ... and ok

d) Ctrl-Z

e) idem c), but select table row 2 (A2:AE2), right button, insert, table rows up... and ok

f) Ctrl-Z

g) idem e), but copying and inserting... and ok


And that's all, folks!

Just advise if need further help.

Or upload the real file, and I'll send you back updated.


Regards!
 
Hi SirJB7,


I have tried to sort it myself but dont seem to be able to do it and I am getting very frustrated. Would you mind trying to sort it out for me? I didn't seem to be able to access the file you uploaded last time, is there an easier way for me to download it?


http://hotfile.com/dl/143885264/bea2939/Engagement_tracker_2012.xlsx.html


Thanks again for your help
 
Hi, charlesbaxter!

I've clicked on the link I posted earlier and I could download it from a different machine. Do you get enabled a light blue "Download button" nearly at the bottom of the page?

If you can't get it, I uploaded it to my skydrive:

https://skydrive.live.com/?cid=3a8bdc8cdf4d772c#!/view.aspx?cid=3A8BDC8CDF4D772C&resid=3A8BDC8CDF4D772C!267

If you can't neither, please consider posting an e-mail address here so as I can send it you by mail.

Regards!
 
Hi, charlesbaxter!

The link was the full line, you should copy and paste in the browser, not just the part that appears as a hyperlink (skydrive always behaves like that).

Just emailed you.

Regards!
 
You must log in or register to reply here.
Back
Top