• 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.

How can I change the Green to Red in this cell

Jack-P-Winner

Jack-P-Winner

Member
If you click on the tab #54 Bingo and look at cell G17 you will see the cell has a 5 and is Green. The 4 digit counter number in C17,D17,E17 and F17 is 4346. How can I change cell G17 from green to white and make cell H17 Red? Also, if you go to the next tab #55 Bingo 4-10-13 and look at the same cell G17 you will see a 6 in that cell. Depending which colored cell (red or green) gets a color the next number goes up. So this went from 5 in G17 a #54 to 6 in G17 tab #55.

My question is I want to change the G17 on tab #54 to white. Cell H17 to red and that should make cell H17 tab #55 from a 5 to a 6 and the 6 win G17 same sheet (#55) go back to 5.

Thanks for your help
 

Attachments

  • Bingo Narrowing Down.xlsx
    340 KB · Views: 10
Hi ,

Just change A17 on tab #54 from 50 to 49. This will change the CF of G17 and H17 so that G17 becomes white and H17 becomes RED.

However , the data in column A is from an external workbook , and without looking at that workbook it is difficult to say how the values in tab #55 will reflect any change in tab #54.

Narayan
 
Thanks Nan, I know I can do that and you are correct about the external sheets. Let me if I can upload a sample of an external sheet. they are massive in size so may have to do an original
OR, I will upload a totally different file from this one (has same issue) with its sister external file

I know I can change the cell number but where and how. That is my issue, I can not seem to figure out where that change is (I forget) I also know that if I change it to a -1 it will change but that is a band aid

I will upload another file that has the same issue just multiple sheets
 
Last edited:
I started this one last week but was not getting much help so I decided to ask a different way. But you can see this thread too for more info. Same issue like I said just asked differently with different sample sheets

http://forum.chandoo.org/threads/expain-this-code-to-me-please.16158/#post-98091

Here is the file. This file is newer than the one I posted earlier after combining one of the external sheets to one. The drawn numbers are built in this book where as the one above is separate.

https://www.dropbox.com/s/tk72adpbur8dtbx/game 41 filled out Numbers Input.xlsm

Hope all this makes sense
 
Hi ,

Sorry , but your world seems to be a different one ! I can't even imagine one worksheet filled up like this , and you seem to have dozens of them !

If we take your last uploaded file game 41 filled out Numbers Input.xlsm , it refers to several external workbooks , at least 4 of them.

If we take the first file you uploaded viz. Bingo Narrowing Down.xlsx , it again refers to 4 external workbooks , but these 4 are different from the 4 referred to in the file game 41 filled out Numbers Input.xlsm.

Unless you uploaded one set of files , the files which are the source and the one which uses them , then we can deduce the complete dependency tree ; as it stands , the dependencies I can see are as follows :

G17 is dependent on BC17

BC17 is dependent on C17

C17 is dependent on data in this external workbook :

'E:\Users\LuckyJackPotWinner\Desktop\PowerBall\Old Style Books\OB Hit Numbers\[(OB Hit Numbers Even - Odd).xlsx]#1A-EO'!R249

Unless we have this file , we cannot traceback any further.

However , to complicate matters , the colouring of G17 green is according to the value in A17 !

A17 is dependent on data in this external workbook :

'E:\Users\LuckyJackPotWinner\Desktop\PowerBall\Old Style Books\[(Numbers).xlsx]

Unfortunately , the sheet referred to in the above workbook is Sheet1 , and the uploaded file with the same name does not have a tab named Sheet1 !

Narayan
 
so sorry and you are correct and my goal is not to complicate things. I was only using the other file as a comparison that I have the same issues with every book. What you are saying as the tree is exactly what I am looking for what goes to where. These other external books are 122 kb each and there are 4 books that all link back to a book like this depending on which book I am using. I can possibly empty all the data from the book(s) but they will still be large. I REALLY do appreciate the help as I NEED to fix this so I can be 100% accurate. If you can bear with me I will delete one of these and only use ONE set and provide the other external book that is needed. You would not need all 4 books because if I can get just one cell fixed I can fix the rest. I was having trouble connecting the dots as my brain is in overload as you can imagine just by what you have seen I have AGAIN THANK YOU!!!! for your help!!!!!!
 
+I can not delete the files above but I deleted them from dropbox. The concept is the same but use THESE books and ONLY these books please and once again THANK YOU THANK YOU THANK YOU for your help!!!!!

Here is the Input book for Game 41
https://www.dropbox.com/s/1qs4qhl56q6s1iu/Game 41 Numbers Input.xlsm

Here is the external book for the 50+ area(s) which will be rows 9-14

https://www.dropbox.com/s/w0z0e63ufd25toh/Game 41 External Book 50+ Hit Numbers Book.xlsx

The only other books go to the other rows which are separate from these

When you open them enable Macros and save a file. If you have to link them together the last page is where you have to go not to mess it up. You will go to the 50+ book last page and click on data and then link to the Input book. The input book you have to link from the INPUT page and link to the 50+ book. They should auto link

This is a total different book than the last but its the same structure. The two are identical except the errors may be in a different place. I just need to know how to change the color cells so I can change the ones that are not correct

In this book you will notice that in game 40 in row 12 columns RS there is not a color in that cell. One or the other needs to be in there. I know from reading the counters from game 40 to 41 it should be red but I need to see where and how to make that cell red. Once I can get help on that I can figure out the one blow that in row 4 and 20. Game 41 same issue with row 12 columns RS. I have issues like this all over and some I need to change the red to a green. I hope this makes sense. If I can just get a road map like you did above in post #6 that helps tremendously and then "how do I change them"
 
Last edited:
Hi ,

Thanks for the upload ; the dependencies are extremely complicated.

If we look at the CF for R12 and S12 , the rule for changing R12 to GREEN are : =$EX12=1 , while the rule for changing S12 to RED are : =$EZ12=1.

EX12 has a monster IF formula :

Code: 
=IF(EJ12="?","",IF(EJ12>EJ13,1,IF(AND(EJ12=EJ13,EJ12>EJ14),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12>EJ15),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12>EJ16),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12>EJ17),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12>EJ18),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12>EJ19),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12>EJ20),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12>EJ21),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12=EJ21,EJ12>EJ22),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12=EJ21,EJ12=EJ22,EJ12>EJ23),1,""))))))))))))

where the cells EJ12 through EJ23 have the following values :

15 , 17 , 5 , 12 , 11 , 9 , 14 , 6 , 13 , 14 , 6 , 5

The above values , in turn are drawn from the cells N11 through N23 on the Drawn Numbers tab.

These numbers in turn come from the cells E11 through E23 on the hidden tab.

E11 has another monster formula :
Code: 
=(COUNTIF(C9:C107,F11)+COUNTIF(F9:F107,F11)+COUNTIF(I9:I107,F11)+COUNTIF(L9:L107,F11)+COUNTIF(Y9:Y107,F11)+COUNTIF(Z9:Z107,F11)-(COUNTIF(C9:C10,F11)+COUNTIF(F9:F10,F11)+COUNTIF(I9:I10,F11)+COUNTIF(L9:L10,F11)+COUNTIF(Y9:Y10,F11)+COUNTIF(Z9:Z10,F11)))

where the value in F11 comes from the formula : =IF(Input!J11="?","?",Input!C11)

If we go to the Input tab , C11 comes from a formula : =SMALL(I11:L11,{1,2,3,4})

which means , the starting point for all of this is the series of 4 numbers in I11 through L11. Only you can say what the relationship between these numbers is , and how they can be changed to ultimately change the colour of the cells R12 and S12.

Narayan
 
Excellent Nan, and this does make sense to me but one thing is that I am back to my original question on how can I change the color of the cell. Let me explain. The 4 numbers in I11 through L11 are numbers that I physically put in and are dictated to me.(I have to put whatever numbers is given) I know if I change I11 to a 7 it will change the cell color because I am going from a Even number to an Odd. I need to find out how to change the color of the cell another way besides changing any values in I11-L11. I will look through what you wrote with a fine tooth comb and do some comparisons against another sheet that has a good output in that cell and see if the formulas are the same. This road map is a Hugh part of what I needed so you have been a HUGH help!!!! THANK YOU!!!! YOu are AWESOME Narayan!!!!
 
Nar,

which sheet is this from?

These numbers in turn come from the cells E11 through E23 on the hidden tab.

E11 has another monster formula :
Code (text):

=(COUNTIF(C9:C107,F11)+COUNTIF(F9:F107,F11)+COUNTIF(I9:I107,F11)+COUNTIF(L9:L107,F11)+COUNTIF(Y9:Y107,F11)+COUNTIF(Z9:Z107,F11)-(COUNTIF(C9:C10,F11)+COUNTIF(F9:F10,F11)+COUNTIF(I9:I10,F11)+COUNTIF(L9:L10,F11)+COUNTIF(Y9:Y10,F11)+COUNTIF(Z9:Z10,F11)))
Click to expand...

I have looked at all the E11's on the sheets and I must be missing one

On the Drawn Input page E11 has the following

=ABS(LEFT(F11,1)-IF(RIGHT(F11,1)="0",10,RIGHT(F11,1)))

Just an FYI In Game 40 RS12 is linked to the external book 50+ tab UD-EO#2 row 12 and 13

You can actually change the drawn number in the Input tab J12 to say 5 and you will see in game 40 R12 change to green bur again, Once I get the drawn numbers I can not change them so there has to be a way to physically change a value somewhere else to change the cell(s) RS12. That has been my stumbling block

Thanks for your patience and help!!!
 
Hi ,

The formula in EJ12 on the Game #40 tab refers to N12 on the Drawn Numbers tab.

The latter in turn has the following formula :

='DO NOT DELETE OR ALTER!!!!!!!!!'!E12

where the tab DO NOT DELETE OR ALTER!!!!!!!!! is a hidden tab.

E12 on this tab has this formula :

=(COUNTIF(C10:C108,F12)+COUNTIF(F10:F108,F12)+COUNTIF(I10:I108,F12)+COUNTIF(L10:L108,F12)+COUNTIF(Y10:Y108,F12)+COUNTIF(Z10:Z108,F12)-(COUNTIF(C10:C11,F12)+COUNTIF(F10:F11,F12)+COUNTIF(I10:I11,F12)+COUNTIF(L10:L11,F12)+COUNTIF(Y10:Y11,F12)+COUNTIF(Z10:Z11,F12)))

where all of the referenced addresses such as C10:C108 , F12 , F10:F108 , I10:I108 ,... all refer to cells on the Input tab. Only Y10:Y108 and Z10:Z108 do not have any data ; I assume this is OK.

Narayan
 
yes sir, this is correct. I forget where it was that I could actually change the cell reference colors and numbers in the cells I am speaking about UG...
 
NARAYANK991 said:
Hi ,

The formula in EJ12 on the Game #40 tab refers to N12 on the Drawn Numbers tab.

The latter in turn has the following formula :

='DO NOT DELETE OR ALTER!!!!!!!!!'!E12

where the tab DO NOT DELETE OR ALTER!!!!!!!!! is a hidden tab.

E12 on this tab has this formula :

=(COUNTIF(C10:C108,F12)+COUNTIF(F10:F108,F12)+COUNTIF(I10:I108,F12)+COUNTIF(L10:L108,F12)+COUNTIF(Y10:Y108,F12)+COUNTIF(Z10:Z108,F12)-(COUNTIF(C10:C11,F12)+COUNTIF(F10:F11,F12)+COUNTIF(I10:I11,F12)+COUNTIF(L10:L11,F12)+COUNTIF(Y10:Y11,F12)+COUNTIF(Z10:Z11,F12)))

where all of the referenced addresses such as C10:C108 , F12 , F10:F108 , I10:I108 ,... all refer to cells on the Input tab. Only Y10:Y108 and Z10:Z108 do not have any data ; I assume this is OK.

Narayan
Click to expand...


Just so you know. Y10:Y108 and Z10:Z108 do not have any data on these books. I have other books that have an additional number as in BMX that would be #5 and/or #6. Tis way all I have to do is add or delete a row or two instead of reprogramming the entire code

Is there not a way to change the cells RS12 without changing the drawn number? I know its there because I have done it a few years back .... I just cant remember. Now, I keep better notes
 
Hi ,

I'll take a more detailed look at it , and get back to you.

You are saying that without changing any of the numbers in the range I12:L12 on the Input tab , you still wish to change the colour of cell R12 on the individual Game tabs to GREEN ; is this what you want ?

Narayan
 
Is it possible to change one of the numeric values?


=IF(EJ12="?","",IF(EJ12>EJ13,1,IF(AND(EJ12=EJ13,EJ12>EJ14),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12>EJ15),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12>EJ16),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12>EJ17),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12>EJ18),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12>EJ19),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12>EJ20),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12>EJ21),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12=EJ21,EJ12>EJ22),1,IF(AND(EJ12=EJ13,EJ12=EJ14,EJ12=EJ15,EJ12=EJ16,EJ12=EJ17,EJ12=EJ18,EJ12=EJ19,EJ12=EJ20,EJ12=EJ21,EJ12=EJ22,EJ12>EJ23),1,""))))))))))))
where the cells EJ12 through EJ23 have the following values :

"15 , 17 , 5 , 12 , 11 , 9 , 14 , 6 , 13 , 14 , 6 , 5" ( numeric value )

The above values , in turn are drawn from the cells N11 through N23 on the Drawn Numbers tab.

These numbers in turn come from the cells E11 through E23 on the hidden tab.

E11 has another monster formula :
Code (text):
 
Hi ,

So far , you have uploaded 3 different files as follows :

1. Game 41 Numbers Input.xlsm

2. game 41 filled out Numbers Input.xlsm

3. Game 41 Nar Numbers Input.xlsm

I have seen that the formulae are the same in all 3 files , but the colouring is different ; we need to go through the relevant cells to see how things change between the files. Will take some time.

Narayan
 
You are corect. I've made some corrections but the issue is the same no matter which book(s). It is. I have the sane issue no matter which book How do you change the cells from green to red?
 
Again, THANK YOU! I have spent days trying to figure thus out. I will google what relative cells are I think I know but want to make sure. This how they get there color

When I enter a draw number it updates ALL the sheets and places a new counter number(s) in the external sheets I have 3 external books for this 1 book. The books are called Numbers, 50+ and OBHITS. The numbers book is always correct it's pretty straight forward (is the bit number higher or lower, even or odd) the next 2 books are doing the hard crunching taking away numbers Anyway....for example if you open the external book and go to the E-O sheets (they all work the same) or the EO/EEOO (they all work the same) when the new counter is placed in STUV (E-O) or WXYZ (EO,EEOO) if it single digit number goes up in SU it will go Even, in TV Odd. In WY it will go up, in XZ it will go down These counters always go up 1-2-4-4-5-6-7-8-9-1. After 9 it starts over again to 1 So 1234 if even or up will change to 2234 or 1244 depending upon if it's on an even game or odd game (column A). Green is even, red is odd) if 1234 is odd or down the new counter number will be 1334 or 1235. Okay, still with me? Once a new counter number is placed the counters are linked to the Input book. All the books are linked together. If you look at the input book under columns AY- BF ( should be un hidden) you will see for example under AY it's the same as columns GH, BB is the same as RS and BE and BF is the same as AC, AD. From this point I'm clueless.

There is one other hidden sheet calked DO NIT DELETE OR ALTER) that has a hidden formula off to the right. It should be unhidden I will continue all day working on trying different things I could upload another book with all the cells marked with errors but don't want to have book number 4.

Like I said, if. Can figure out be cell I can do the rest.

Thank you so much!!!!
 
This is what I found. Its all in how the calculations are done from the counters in the external sheets compared to the Input sheet. I had some help putting this together and its obviously wrong. I will post what the EO and UD should be. I use to do 1 Input sheet at a time inputting each cell so I wanted to design a book to incorporate 10 games with a reset. The objective is to be able to play up to 10 games in this book and no matter where you reset it .... it should take off where it left. In other words, lets say I have a game in Game 50 and I get EEEO from the counters (50+ E-O 1-2-3-4) they if I reset the game to 49-48-47-46 ..... it should follow suit and be EEEO. Well, its not . I can figure out how it should be just by inputting the draw numbers say in game 48 and then start filling out the EO and UD in Game 49 to equal the draw number you inputted in the Input sheet

I will upload or place in this forum what each cell "should be"
 
You must log in or register to reply here.
Back
Top