Replies

Rory,

Manually using the start menu.

liz

To Myself,

if you have a column with the hours and minutes and do a normal addition it will work
if you need to add to a date in a formula with a value in a formatted column I don’t think it can be done

liz

hmmm…
It is odd that the space before is OK for teh first instance but then not honoured, I have no idea why this is a problem.

As far as the shading goes my solution thus far matches what Andrew suggested so it looks like this:

[INDENT]HeadingTopMargin, page break before, no numbering, keep with next; soft return
soft return
soft return (I hate doing it this way but now I get solid colour)
Heading 1, no top margin no page beak before[/INDENT]

not perfect but it works

Thank you guys

liz

Yes exactly, each of my pages is in a section of it’s own and the layut is defined as vertically centred. It always used to work.????

Hi Charles,

Thank you, I know this well and should have reread my own notes based on Shauna’s excellent guide. I have been away from Word for a while and so got a bit rusty. I thought I was doing the right thing but then realised that I MUST create the “list style” (originally called a list template I think, hence my confusion now it is a “style”) then link all the paragraph styles. NOT as i was doing it creating a new multi-level-list

liz

Hi Steve,

The formatting was originally set-up manually and worked, the macro was developed based on recording and some tweaks to optimise it a little. The rules look correct after the macro has run. The only odd think is how the range displayed in the rules dialogue shows and doesn’t show when I scroll – there’s no logic. the attached file has two screen dumps they are the same area just scrolled up and down (it feels like a memory-disply conflict but can’t be). Whether the area is populated in the rules seems to be random, but it is obviously applied correctly when the macro runs.

Mystified????

liz

Hi Steve,

Yes, I understand what it is doing. And although I know that is how it behaves it isn’t the way most people (in mt experience) want Excel to behave. Hence the macro. The behaviour it isn’t consistent with normal range behaviour – if you introduce something in the middle of a range it is part of it and adopts other characteristics – OK a rule isn’t a range but one could reasonably expect this to work similarly.

Leaving my gripe about the behaviour od Excel I come back to the VBA. The problem I have is that there are several conditional formats, probaly 10 or 12, the sheet eventually grows to 1000 lines. We are carefule about inserting rows and the VBA sorts that out. That isn’t the problem, this is:

[*]to be sure if i manually clear all conditional formats so I know it’s clean
[*]apply the vba to add conditional formats which run to line 1000 or so
[*]go to the next blank line and complete some details
[*]RESULT: conditional formatting not applied even though it shows in the rules manager

Note I am not copying, inserting or deleing simple typing in the next available row. The formatting should apply according to the rules manager.

liz

Hi Steve,

The original problem is that, depending how things are inserted or amended, you can end up with a huge number of conditional format rules that amount to the right thing – effected by a number of rules applied to several ranges that together might be the original range expanded or reduced as you intended. This means the sheet will behave as you expect until it doesn’t and the rules, by then, have multiplied to make maintenance impossible. In the attached spreadsheet I copied line 72 and pasted it to reused it in line 75. If you check the rules for the sheet they have started to disaggregate so we now have many rules.

So I come back to the “real” problem which is how to make the VBA “stick” once applied, it behaved correctly, the rules look correct but they do not get applied as I change data I have to rerun the VBA.

Thank you

liz

OK found it, the format wasn’t set to a number for the cell

Excel 2010

here’s the sheet, nothing confidential in it the Summary tab is the problem and it’s working off the Config progress tab

Thank you…………………. liz

Thank you Rory.

However, my sheet has two pivot tables, they work from the same data just showing slightly different summaries. The Column heading will be identical.
So, following your idea I had some rows with the status set so that all status values were used, the pivot tables showed all values, perfect! After I deleted the rows with the values the first table still shows all status values in the column headings but the second only shows those used. I used Alt F5 to update them. I can’t see any settings that are different for each table other than the row labels.

Just as an aside the second table also loses one of the vertical grid lines every time where the other table doesn’t. And generally it doesn’t seem possible to make any formatting like column widths stick… or is it?

Thank you………………… liz

fab!!!!

This seems to work:

=INDEX(E4:E200,COUNTA(E4:E200),1)

It doesn’t cope if the sequence has cells with blank in them but is good enough

Hi Maud,

Thank you.

The reason i wanted a formula is that i need to do it several times and display the result in a cell at the top of the column in question. I am trying it with index and match but never quite do it right so I am hoping someone will help – likely to be RetiredGeek who is one of the most generous members here

liz

Hi Retired Geek,

How annoying obvious! I was going blind having stared at it for so long. Thank you

My problem then was how to wrap INDIRECT around it, I had a problem with quotation marks. Partly because I thought I had to have single quotation marks round the tab name – I removed embedded spaces and then was OK.

For anything with this problem here is the solution:

[INDENT]=INDIRECT(PreviousTabName&”!”&ADDRESS(ROW(),COLUMN( ) ))[/INDENT]

liz