How to prevent additional entries in formatted table from overwriting cells below

[Bimdorf]

Excel 2007:

I have several tables identically formatted using the "Format as Table" feature. These tables are positioned one below another in one worksheet, with one blank row separating each table.

If I were to add data to the bottom of any particular table, that row would automatically format to match the rest of the table, but it does not automatically maintain spacing between that table and the next table below. If I were to add a second row of data to that table, the table would begin to overwrite the contents of the table below.

Is there a way to maintain spacing of one row between tables without having to manually insert a row in the worksheet?

Thanks.

 

[GlennUK]

Can I ask why you aren't storing your several tables in several sheets?

 

[Bimdorf]

Each separate table represents an action plan under a specific category heading.

In this case, it represents a care plan for an elderly patient receiving in home care, and the categories would be Medical, Social, Hygiene, Financial, etc. Under each category, there are 5 columns: Concern, Action, Responsible Party, Outcome, and Notes. There might be other categories as well, and with my limited knowledge, I've created a macro to create and format an additional table starting two rows below the last populated row:

. . . . . . . . . . . . . . . . . . . . . . . . MEDICAL
-----------------------------------------------------------------------
Concern. . Action. . . . . . Responsible Party. . . Outcome. . . . . . . Notes
blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah
blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah
[new blah]
. . . . . . . . . . . . . . . . . . . . . . . . SOCIAL
-----------------------------------------------------------------------
Concern. . Action. . . . . . Responsible Party. . . Outcome. . . . . . . Notes
blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah
blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah

With my tables automatically formatted, the user can easily add more data to the bottom of each separate table, and retain formatting, and sort as needed, etc., but the problem is when data is added, Excel doesn't insert a row, but overwrites instead. In my illustration above, If I were to add data to the MEDICAL category, I would first lose the blank line above SOCIAL, and if I were to add another line, I would not only overwrite SOCIAL, but the data entered would take on the formatting of the SOCIAL table header.

I'm trying to keep all the data on one sheet so the user can enter data in a single WYSIWYG environment. This sheet will also be printed for others to reference as well. I also want the worksheet to be as idiot proof as possible.

I'm open to suggestions; I'm learning quite a bit as I go, and if you have a more efficient/more user-friendly way, I'd greatly appreciate it. I've thought about using a UserForm for each concern (with the user specifying the category) and generating a report afterwards, but I only discovered userforms this afternoon.

Thanks in advance.

 

[GlennUK]

This:

I'm trying to keep all the data on one sheet so the user can enter data in a single WYSIWYG environment.

... is not a sufficient reason to have the tables in one sheet. The users have to scroll to get to the relevant table, instead of simply choosing the appropriate tab. All your other problems disappear completely if you follow this route.

 

[Bimdorf]

Well, not completely...

If I have multiple worksheets and tabs(I already have seven, which handle other aspects of elder care, such as contact info, prescriptions, case log, billable hours etc.), I would now be asking the user to not only create a variable number of new worksheets and keep them organized among the other worksheet tabs (because some, but not all categories will be used), but to somehow assemble them for printing.

I see your point about separating the tables, but perhaps a better solution would be to have only one monolithic table.

If I used one table, putting the category in the first column as follows:

Category. .Concern. . Action. . . . . . Responsible Party. . . Outcome. . . . . . . Notes
Medical . . .blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah
Social . . . .blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah
Medical . . .blah. . . . . blah. . . . . . . .blah blah. . . . . . . . .blah blah blah. . . . .blah

How could I generate a printed report formatted with the category spanning the other rows, like in the original example? I see that as intrinsically easier. I could even create a userform to handle the input of data, and the table itself could be hidden from user view, but I am stumped on the formatting when it comes time to generate a report.

Essentially, all I would need to do is:

1) sort by category (col A),
2) hide the value in col A
3) make the value of each col A grouping span the other columns as a category heading
4) insert a space before new category headings

I know making reports like this from tables is super easy in MS Access, but how is it done in Excel?

 

[GlennUK]

What do you mean "but to somehow assemble them for printing"?

You can print more than one sheet at a time.

As for your other idea, to have one monolithic table would work, apart from the reporting shortfalls that you have identified. Sorting by column A would be easy, but the kind of reporting that you want is not really the way that Excel works ... like you say, it's easy in Access ... if you'd prefer Access, then go that route.

 

[Bimdorf]

What do you mean "but to somehow assemble them for printing"?

You can print more than one sheet at a time.

I don't want each category to appear on its own sheet of paper when printed. Moreover, the presence of up to 8 additional tabs will serve to confuse my users (trust me - they're easily confused).

As for Access, yes- it would work, but that requires knowledge of Access, which I don't currently have. I'm having a tough enough time with Excel as it is.

Everything currently works. My only risk is that users will overwrite category headings.

I think I'll just figure out a macro to insert a line if any cell in the row above each heading becomes populated and call it good for the time being.


Ciao!