Hi,
I had a file I created last year. In it I created sheet for each month, and added a table (Ctrl+T).
It worked lovely for last year.
This year I dupliceted the file, deleted the data in each table, and left only 2 blank row in each table/sheet.
The first column has a formula from row 2 onward to count the rows (the sheet has to be printed.
After the duplication I found that the formula does not drag to new lines opened.
I fixed this issue by converting all Tables to Ranges, and back to Tables.
This stupid workaround fixed the formula issue, but not the properties of the different cells for the new rows I open.
I attached the file in question. If you open a new line in sheet "Jan" you will see that the alternation coloring is not kept.
In sheet "Don Jan-Jul", if you open a new line, you can see that the formating (color and font) is not kept.
Open new line = tab all the way to the end of current row, and another tab to open a new line.
Any suggestions?
Regards,
Ayal Telem.
You've got manual formatting applied to some cells (eg a light blue fill on the Jan sheet). You should remove that.
Dear Velouria,
I did a clear fill before, but I guess I never did it for the whole table.
Once I cleared the fill from all the table it worked OK.
I have attached another file (actually a subset of the previous with the sheet "Don Jan-Jul" perviously mentioned).
In this file, when you open a new row, the "Date" and "Amount" columns not retaining their properties.
Regards.
I formatted both table columns based on the formats displayed for the first 4 rows. Amount as a two decimal number and Date with a custom format "d/m".
Added a row and some data and the formats remain as intended. See attached.
By the way, I suggest you also use structured table references when you sum columns in an Excel table.
Done that in J2.
Dear Riny,
Thanks for the reply.
I can't understand what you did differently from me, because I also formatted all columns as I wanted, but it failed to retain formatting.
For some reason, when I tried to apply it to my original file it suddenly started to work.
I don't remember how I applied the formatting before, but this time I used the fromatting brush (Right Click) and applied it to all cells in the columns.
Thanks for the tip regarding the sum also.
Regards,
Ayal Telem.
Hi,
Soooo, after a long time I found the time to continue the subject.
In the attached file you can see in sheet "Apr" that although lines 17&18 of column B have the same properties as the lines above, they don't look the same, and they produce error in sheet "All_Data".
Also, I can't figure out why some tables in this file retains cells properties for new lines, and some don't.
Some I can fix, and some I can't.
You can see that even the font of the cells are not retain.
Briefly speaking, a lot of frustration.
Regards,
Ayal Telem.
Most entries in column B are texts where some are real dates. Texts are left-aligned and real dates are right-aligned.
Can't tell why the font doesn't remain to be Calibri 10 in your case. Selected the entire table, set the font to Calibri 10 and started adding rows. They all keep the correct font.
Dear Riny,
When I look at items in column B, all are Custom "dd/mm", as you can see in the attached files.
I also checked on the 365-web to make sure it's not an installation issue.
Regards,
Ayal Telem.
Formatting a cell as DD/MM does not turn the value into a date automatically. Real dates in Excel are sequential numbers, starting at 1 for January 1, 1900. Todays date is day number 45417.
Check your dates by formatting the date column as General. Real dates change to a number. Text will remain as they are. Or, use either of the ISNUMBER or ISTEXT functions to verify the true nature of the dates.
