• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format the numbers to shade and color code

Hi Larry ,

What do you have in that book ?!

Downloading it is itself going to take some time !

I'll put the formulae in , and re-upload it.

Narayan
 
Narayan,

Thus is one of the 5 books I was talking about. They're almost 2 Gig each I know they're HUGH. The books are full of counters and firmulas Basically the books give me the results like the example you worked on for me

Can you put a formula in the example sheet you already worked on? I can yes it from this book and copy it to the large books
 
WOW!!! Again, you never cease to amaze me !!! EXCELLENT and working great. Question but NOT a big deal, in the last few rows I get a REF and do not know why since all the others are fine
 

Attachments

  • ref for side num.png
    ref for side num.png
    99.9 KB · Views: 1
Hi Larry ,

The problem is that the lowest row in the range of columns S through V ( S3001 , T3001 , U3001 and V3001 ) have error values in them ; if these are eliminated , then the resulting error values in other dependent cells will also be eliminated.

Regards ,



Narayan
 
Narayan,

How can I get this one to do what you did on the other sheets? I tried switching the columns and rows but failed. I can not upload a file from my ipad so can you write the code on here for me to copy and paste?

Thank you so much!!!
 

Attachments

  • 2014-08-07_19-22-12.png
    2014-08-07_19-22-12.png
    127.1 KB · Views: 4
Hi Larry ,

Here goes :

1. The lowest row in your helper columns will have 1s in the four helper columns which correspond to your data columns.

  • Suppose your data columns are F , G , H and I.
  • Suppose the starting row for Game 1 is the worksheet row numbered 2700.
  • Suppose the helper columns are BA , BB , BC , BD and BE.
Thus the cells BA2700 , BB2700 , BC2700 and BD2700 will all have 1 in them.

2. Suppose the generated numbers are in column C.

3. If there is no transition in the next higher row , then put 0 in C2700 , else put 1 in C2700.

4. In BE2700 , enter the following formula :

=ISODD(IFERROR(INDEX($F2700:$I2700,MATCH($C2700,$BA2700:$BD2700,0)),1))

Copy this formula to BE2699.

5. In C2699 , enter the following formula :

=SUMPRODUCT((BA2699:BD2699<>1)*(BA2698:BD2698=1),BA2699:BD2699)

6. In BA2699 , enter the following formula :

=IF(F2699=F2700,BA2700+1,1)

Copy this formula across to BB2699 through BD2699.

7. Copy the formulae in C2699 , BA2699 through BE2699 upwards as far as you want.

8. Suppose you copy all of the formulae till worksheet row number 7.

Select the range C7:C2700 ; click on Conditional Formatting , and enter the following formula for the RED colour :

=$BE7

Enter the following formula for GREEN colour :

=NOT($BE7)

Try all of the above and let me know if it works.

Narayan
 
You must log in or register to reply here.
Back
Top