Double Trouble

[boakley]

Hi everyone.

I have two issues that I think a lot of you would be able to help with.

Issue#1: Conditional Formatting

I have 3 cells in different location in the same row that need to be conditionally formatted. Each of the three cells already have a formula in them. End state, I want the cell to turn green if the value in the cell is >=50 and I want it to turn red if the value is<=49. I have already played with it and got it to work but once I take the data out of the cell, it stays green. I can only imagine that it has something to do with the formula that is already in the cell. The cells are Cells G4, I4, and K4. The formulas for each cell under below.Any help?

(G4): =IF(D4="M",INDEX(MALE_PU!B2:AO117,MATCH(F4,MALE_PU!A2:A117,0),MATCH(E4,MALE_PU!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_PU!B2:AO117,MATCH(F4,FEMALE_PU!A2:A117,0),MATCH(E4,FEMALE_PU!B1:AO1,0)),""))

(I4): =IF(D4="M",INDEX(MALE_SU!B2:AO121,MATCH(H4,MALE_SU!A2:A121,0),MATCH(E4,MALE_SU!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_SU!B2:AO117,MATCH(H4,FEMALE_SU!A2:A121,0),MATCH(E4,FEMALE_SU!B1:AO1,0)),""))

(K4): =IF(D4="M",INDEX(MALE_RUN!B2:AO614,MATCH(J4,MALE_RUN!A2:A614,0),MATCH(E4,MALE_RUN!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_RUN!B2:AO584,MATCH(J4,FEMALE_RUN!A2:A584,0),MATCH(E4,FEMALE_RUN!B1:AO1,0)),""))


Issue#2: In the same cells previously stated in "Issue#1", I need those cells to convert some numbers appropriately. So if cell G4 has a value of 101, I4 has a value of 103 and K4 has a value of 98... I need cells G4 and I4 to automatically change to 100 instead of the values that were over 100, simply because K4 did not meet the "100" mark. The same goes with the other two cells. If there is one or more cells out of those three that did not break 100 or more, then I need the cells that did break 100 or more to return themselves back to "100".

Thank you in advance for the help!!

 

[MartyS]

Can I ask for clarity on the first part of what you said?

Could you confirm:

I want the cell to turn GREEN if the value is GREATER (or equal) to 50

I want the cell to turn RED if the value is LESS than 50

When I take data out of the cell it turns GREEN???


Unless you've got additional conditional formatting in those cells, that doesn't make sense...

I could understand it staying RED when you take data out, based on those rules...


Taking data out of a cell, or putting a <SPACE> character (" ") into a cell is still evaluated by the conditional formatting, and (numerically) is evaluated as ZERO.

Zero meets the criteria of "less than or equal to 49" - so the cell is formatted that colour.


You could try the Cond. Formatting formula


For values of 50 or above

=AND(LEN(TRIM(G4))>0, G4>=50)


For values of 49 or less

=AND(LEN(TRIM(G1))>0, G1<=49)


This evaluates the LENGTH of the contents in the cell and will only Conditionally Format if the cell has data in it with a length of 1 or more.

Also, the TRIM part removes "spaces", so if someone deletes data using spaces instead of the delete key to clear the contents, this is excluded from the conditional formatting.

Hope that helps.

 

[MartyS]

Hi everyone.


Issue#2: In the same cells previously stated in "Issue#1", I need those cells to convert some numbers appropriately. So if cell G4 has a value of 101, I4 has a value of 103 and K4 has a value of 98... I need cells G4 and I4 to automatically change to 100 instead of the values that were over 100, simply because K4 did not meet the "100" mark. The same goes with the other two cells. If there is one or more cells out of those three that did not break 100 or more, then I need the cells that did break 100 or more to return themselves back to "100".

Thank you in advance for the help!!

I think this would cause a Circular Reference.

A's calculation would be based on B and C....

but

B's calculation would be based on A .. (now a circ. ref.) and C

and C.. would be on A and B - both of which would be circ. refs.




However, if you use another 3 cells and reference the originals.. no problem - that seems to work a treat.

To check: the three cells you're referring to - you don't enter data into them, they have the formulae you mentioned in them..



Let's say you had this:

Creating another 3 cells that contain the following, respectively would give you what you want without creating a circular reference:



For O62

=IF(AND(SUMPRODUCT(--(P62>=100),--(Q62>=100))<1,O62>100),100,O62)



For P62

=IF(AND(SUMPRODUCT(--(O62>=100),--(Q62>=100))<1,P62>100),100,P62)



For Q62

=IF(AND(SUMPRODUCT(--(O62>=100),--(P62>=100))<1,Q62>100),100,Q62)

You could then hide the original cells, having the additional (drop to 100 if other less than 100) functionality without a circular reference.

Hope that made sense!

 

[boakley]

That worked perfect. Not sure where I had it messed up but it works now!!! Thank you so much!

 

[boakley]

@MartyS

Ok so I entered the formula that you had provided to revert the cells back to the value of "100". It did exactly what I was hoping for. But there is an issue. I entered the formulas you provided into some cells that were not on my table and they worked perfectly. However, I'm not sure how to make them work for the cells that I need them in since there is already formulas in the cells that I need them in. For example:

	F	G	H	I	J	K
3	Push Up Reps	Push Up Score	Sit Up Reps	Sit Up Score	Run Time	Run Score
4	77	100	82	100	1316	100

<tbody>
</tbody>

G4, I4 and K4 have the following formulas:

(G4): =IF(D4="M",INDEX(MALE_PU!B2:AO117,MATCH(F4,MALE_PU!A2:A117,0),MATCH(E4,MALE_PU!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_PU!B2:AO117,MATCH(F4,FEMALE_PU!A2:A117,0),MATCH(E4,FEMALE_PU!B1:AO1,0)),""))

(I4): =IF(D4="M",INDEX(MALE_SU!B2:AO121,MATCH(H4,MALE_SU!A2:A121,0),MATCH(E4,MALE_SU!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_SU!B2:AO117,MATCH(H4,FEMALE_SU!A2:A121,0),MATCH(E4,FEMALE_SU!B1:AO1,0)),""))

(K4): =IF(D4="M",INDEX(MALE_RUN!B2:AO614,MATCH(J4,MALE_RUN!A2:A614,0),MATCH(E4,MALE_RUN!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_RUN!B2:AO584,MATCH(J4,FEMALE_RUN!A2:A584,0),MATCH(E4,FEMALE_RUN!B1:AO1,0)),""))

When I entered the correct amount of reps into cells F4, H4 and J4, the correct score populates into cells G4, I4 and K4, respectively.

If I am understanding you correctly, you are saying to enter the formulas that you provided into separate cells (let's say O62, P62 and Q62 like you provided) away from the table so they can be hidden later. And I would alter the formula you provided to fit the criteria of the cells I need, obviously. But when the data for the formulas in O62, P62 and Q62 is coming from the same cells that I need to get changed back to "100", it doesn't seem to work. I see the cells that I put your formulas into change once my cells are done but I need my cells to get changed. But your formula did work. And I love it. Any idea's?

 

[MartyS]

Ok so I entered the formula that you had provided to revert the cells back to the value of "100". It did exactly what I was hoping for. But there is an issue. I entered the formulas you provided into some cells that were not on my table and they worked perfectly. However, I'm not sure how to make them work for the cells that I need them in since there is already formulas in the cells that I need them in.

If you try to create the formula that you want in row 4 where you currently have formulae, it will create CIRCULAR REFERENCES (i.e. it won't work).


If you look at the earlier post I made, it shows a table have ROW 62 and columns O, P, Q.

This was representative of your sheet: ROW 4 with, say, columns G, I, K


You'll see I added a NEW ROW (ROW 65) and put the formulae in there. Doing it this way avoids the Circular Reference problem.

In your example, you might add ROW 5.

Then:

In cell F5: =F4
In cell G5: =IF(AND(SUMPRODUCT(--(I4>=100),--(K4>=100))<1,G4>100),100,G4)
In cell H5: =H4
In cell I5: =IF(AND(SUMPRODUCT(--(G4>=100),--(K4>=100))<1,I4>100),100,I4)
In cell J5: =J4
In cell K5: =IF(AND(SUMPRODUCT(--(G4>=100),--(I4>=100))<1,K4>100),100,K4)



Once you've put the formulae in, you can HIDE row 4 and the table will visually look the same, but have the functionality you desire.

 

[MartyS]

i.e. like this:

https://www.dropbox.com/s/8zqy5kxi9borl4p/boakley Exercise Spreadsheet.xlsx?dl=0

 

[boakley]

@MartyS

The link didn't work. But I think I see what your saying. Only problem is that I can't really hide Row 4 because there is information that is still needed in that row that needs to be seen. Also if I have F5, H5 and J5 equal to F4, H4 and J4 and then hide row 4, I won't be able to manually input the data I need in those cells for the formula in G4 to work. Is there a way to add my formula with your formula in row 5 and then somehow hide row 5?

 

[MartyS]

@MartyS


The link didn't work. But I think I see what your saying. Only problem is that I can't really hide Row 4 because there is information that is still needed in that row that needs to be seen. Also if I have F5, H5 and J5 equal to F4, H4 and J4 and then hide row 4, I won't be able to manually input the data I need in those cells for the formula in G4 to work. Is there a way to add my formula with your formula in row 5 and then somehow hide row 5?

I just looked at the link and it downloaded ok.. I'm not sure what problems you might be having...





Imagine a really simply spreadsheet.


In cell A4 is the value 10
In cell B4 is the value 20
In cell C4 is the value 30


Go to:


Cell A5 and enter the formula =A4 to reference the cell above it
Cell B5 and enter the formula =B4
Cell C5 and enter the formula =C4


Row 4 can now be hidden.


i.e.

If you have cells in row 4 that you need to enter data into, you can do this.

Put the formula in ROW 4 (as shown in the diagram above). Then you can enter data into the row 5 cell, as shown.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Alternatively, you could move all of your current ROW 4 to ROW 5 (maybe by inserting a row).

Then, put the formulae into ROW 4 (see below)


It's exactly the same thing - only in reverse!


Doing it this way.. with the new-empty row 4, and your existing data/formulae now on row 5, you could put in the following:


In cell F4: =F5
In cell G4: =IF(AND(SUMPRODUCT(--(I5>=100),--(K5>=100))<1,G5>100),100,G5)
In cell H4: =H5
In cell I4: =IF(AND(SUMPRODUCT(--(G5>=100),--(K5>=100))<1,I5>100),100,I5)
In cell J4: =J5
In cell K4: =IF(AND(SUMPRODUCT(--(G5>=100),--(I5>=100))<1,K5>100),100,K5)

and then hide row 5.


Again, this is exactly the same thing! - you're just [hiding Row 5 and keeping Row 4] instead of [keeping Row 5 and hiding Row 4]...

 

[boakley]

Got it!!! Thanks for the help! Its doing what I need it to now!