Automatic sort and Highlight part 2

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

Celticshadow

Hi again

Although the formula works perfectly on a set number of rows I have to alter
it to allow for extra rows or less rows.

As an example if I have data running to 21 rows but I have to go into the
formula bar to manually adjust to incorporate the extra row.
Similarly if the original formula is for 20 rows but I wish to run for only
say 8 rows again I need to manually adjust the formula downwards.

May I ask is it possible for this adjustment to be made automatically.

Kind Regards

Celticshadow





Subject: Re: Automatic sort and Highlight 10/17/2008 8:29 AM PST

By: Celticshadow In: microsoft.public.excel


Dear Roger, Ken and Shane

Many thanks for your replies. I can now confirm that I have sussed the
formula and can also report that it works magnificently, top draw help once
more.

Thanks again.

Celticshadow
 
Hi

Firstly, it is not helpful to start a new thread on this topic, especially
when you don't quote the formula you have finally chosen to use. Other
readers, apart from those who took part in the original thread, will have no
idea what you are talking about.

Ken's final formula
=SUMPRODUCT(--(D$1:D$20<>""),--(D1>D$1:D$20),
1/COUNTIF(D$1:D$20,D$1:D20&""))+1<=6

will ignore blank rows so you could easily set the range to 100 rows
($D$1:$D$100), it will deal with all cases

If you are saying you may want to limit the range of cells which you want to
consider, then you could create a dynamic named range, and insert this in
the formula
In this example, I am using cells A1 and A2 to define the rows to be used.
Change to suit the cell locations you use
Insert>Name>Define>
Name myData
Refers to =INDEX($D:$D,$A$1):INDEX($D:$D,$A$2)
then use
=SUMPRODUCT(--(myData<>""),--(D1>myData),
1/COUNTIF(myData,myData&""))+1<=6

Now, by changing the values in A1 and A2 you can look at any subset of data,
it doesn't have to start at 1, it could be D5:D18
 
Hi All

Please find below the full thread that this part two belongs to. Either
Roger, Ken or Shane’s formula works pertaining to my original question.

I do however have a query that I need solving and is as set out below in
relation to Ken's formula.

I am trying to build a spreadsheet that will automate a horseracing card
into the format and perform certain tasks. Thanks to the help on here I am
almost there. The card is downloaded from the web and is then put into what I
call a transfer sheet that sorts the required data into specific formats.
This data is then transferred to a race card sheet by way of links to the
transfer sheet; from the race card sheet I copy the data into race 1 sheet.

There are six races on each card and I have all 6 race sheets set to 40 rows
and 12 columns with the formula set to 100 rows as instructed below. They
remain blank untill I copy over the data from the race card sheet.

(For example column D ($D$1:$D$100) ).

The first race sheet highlights the data perfectly, however if the second
race to be copied into the second race sheet is shorter or longer in rows
than the first race sheet (the first sheet may contain 10 rows but the second
sheet may contain more or less rows) then the data is not highlighted in the
correct way.

The other 5 blank race sheets show the columns with a full colour highlight
down to the 40th row but when the data is entered into any of these sheets
some of the highlighting disappears and some data is not highlighted at all.
I have looked in the formula bar in the conditional format bar and it does
not show a formula.

Any pointers would be much appreciated.

It is quite hard to explain this in words and thus I feel that it would be
more positive if the ability to attach a picture or file of examples would be
a big plus for this forum. In it's present shape it is not very user friendly
although the help given by contributors is invaluable.

Kind Regards

Celticshadow
 
Hi

Are you selecting the whole of the range to which you want CF applied, then
applying the formula?
When you copy the data from your "race card" to the sheet, are you using
Copy>paste or Copy>paste Special>Values?

If you are using Copy>Paste, then you will destroy and Conditional
Formatting that may have been set for those cells
 
Hi Roger

Yes I have selected the whole range of cells that needs the conditional
formatting for all six sheets and I am using Copy>paste Special>Values.

Kind Regards

Celticshadow
 
It is quite hard to explain this in words and thus I feel that it would be
more positive if the ability to attach a picture or file of examples would be
a big plus for this forum. In it's present shape it is not very user friendly
although the help given by contributors is invaluable.
Click to expand...

You're welcome to email me a sample workbook for me to have a look at.
Get my email from my profile.

Ken Johnson
 
Hi Ken

For those looking in on this thread please note Ken's (below) points
pertaining to the formula.

Ken many thanks that now works a treat and I do not need to go any deeper
than row 43 and thus I will be using your formula. Many thanks to all who
replied, it is very much appreciated.

:


"I've changed to...

=IF(E4="","",SUMPRODUCT(--(E$4:E$43<>""),--(E4>E$4:E$43),1/COUNTIF(E$4:E$43,E$4:E$43&""))+1)<=5
in column E rows 4 to 43, and similar in the other columns with CF.

I noted slightly different endings to the formula in other columns (eg
<=4) and have left those endings in place.

Roger's solution using dynamic named ranges would also work but would likely
require more set up time. You would need to consider using his solution if
the rows of data could go deeper than row 43. I'm assuming that is not the
case".

Kind Regards

Celticshadow
 
Back
Top