[Excel 2011] Insert lines in table with formuals and formatting preserved

[Frank Knappe]

Hello,

simple problem: I have a sheet, where I have a table with formulas and
conditional formatting for each coloumn. Now I have to insert additional
lines inside this table. How can I do this, so that the new cells
inherit the formuals and the conditional formaating from the upper or
lower line?

If I do a normal "insert cells" the range for the conditional formatting
is adapted, but the formulas are not transfered:-(

Thanks in advance.

 

[wabbleknee]

What I usually do is select the cell above, grab the handle (bottom right
corner) and drag it down. Alternative is to look up the format painter and
see how easy that works.

"Frank Knappe" wrote in message

Hello,

simple problem: I have a sheet, where I have a table with formulas and
conditional formatting for each coloumn. Now I have to insert additional
lines inside this table. How can I do this, so that the new cells
inherit the formuals and the conditional formaating from the upper or
lower line?

If I do a normal "insert cells" the range for the conditional formatting
is adapted, but the formulas are not transfered:-(

Thanks in advance.

 

[GS]

Why are these information not transfered?

By default, 'Insert' does not copy formulas from the previous
row/column.

It'll be pretty hard for people using the sheet to forget to copy the
formulas down to inserted rows because it would be quite evident by the
expected results missing in their respective cells, that the formulas
need to be copied to there.


<Not meaning to offend...>
If the concern is that the workbook is used by people with low/little
(if any) Excel skills then why not automate the process so it happens
with the click of a button. This would involve some VBA and a button
added to the sheet. Alternatively, you could request people upgrade
their skills. Not so unreasonable a request if those people need to use
Excel as one of their 'work tools'.

After all, would you trust a mechanic who wasn't proficient with his
work tools to service your car? Or would you trust a surgeon who was
incompetent with tools of surgery to operate on you or your loved
ones?

 

[Gord]

2003 Data>List works the way you want.

2007 Insert>Table works the way you want.

Have they changed this feature in 2010?


Gord Dibben Microsoft Excel MVP

 

[Frank Knappe]

2003 Data>List works the way you want.

2007 Insert>Table works the way you want.

Have they changed this feature in 2010?

I only have the german version and unfortunately I wasn't able to find
these menu points.

There is a menu tab called Insert with a section tables, but this is
doing something completely different.

If I misunterstood you could you please be more specific?

 

[Frank Knappe]

By default, 'Insert' does not copy formulas from the previous
row/column.

Okay, is there a way to make this as a special option?

It'll be pretty hard for people using the sheet to forget to copy the
formulas down to inserted rows because it would be quite evident by the
expected results missing in their respective cells, that the formulas
need to be copied to there.

As we are not talking about simple numbers which appear, it will not be
obvious. For instance I check the time remaining until a deadline and
depending on that there will be conditional formatting. On a short galce
nobody knows if the entry is still not highlighted because the project
is still on-track or because the corresponding formula isn't there at
all.

<Not meaning to offend...>
If the concern is that the workbook is used by people with low/little
(if any) Excel skills then why not automate the process so it happens
with the click of a button. This would involve some VBA and a button
added to the sheet. Alternatively, you could request people upgrade
their skills. Not so unreasonable a request if those people need to use
Excel as one of their 'work tools'.

VBA-Solution:
I'm open to every solution. I prefer Excel-native solutions over VBA,
but if you have an example I would be happy to include it.

 

[Frank Knappe]

I only have the german version and unfortunately I wasn't able to find
these menu points.

There is a menu tab called Insert with a section tables, but this is
doing something completely different.

If I misunterstood you could you please be more specific?

Okay, problem solved. I was able to get the idea behind your proposal. I
was just looking for a way to insert a line. You were given me the steps
beforehand. With the formatting of the data area as a "table", the
normal insert command works like requested.

 

[GS]

Frank Knappe explained :

Okay, is there a way to make this as a special option?


As we are not talking about simple numbers which appear, it will not be
obvious.

I was only referring to the results of formulas, which indeed would be
obvious if they were '*expected but missing*'! This has nothing to do
with ConditionalFormatting unless that formatting was applied to cells
that contained missing formula results.

For instance I check the time remaining until a deadline and
depending on that there will be conditional formatting. On a short galce
nobody knows if the entry is still not highlighted because the project
is still on-track or because the corresponding formula isn't there at
all.



VBA-Solution:
I'm open to every solution. I prefer Excel-native solutions over VBA,
but if you have an example I would be happy to include it.

Ok, Frank. I'm glad one of gord's suggestions worked for you...

 

[Gord]

Good to hear.

Glad you found it.



Gord