These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Google Spreadsheet Advanced Conditional Formatting
Author
Previous Topic Next Topic
Punx Etta
Brutor Tribe
Minmatar Republic
#1 - 2013-03-25 04:08:07 UTC  |  Edited by: Punx Etta
I'm trying to create conditional formatting in a Google Docs spreadsheet that checks the value of a cell in one sheet and colors the the text (or background, I don't really care) of a cell in another sheet according to its value relative to the former cell. For example, if the value in the latter cell is <90% of the value in the former, the color of the latter cell will be red; if <80% the color will be yellow; if >80%, green.

This is fairly straight forward in Excel 2013, as Excel's conditional formatting rules are very customizable. Google docs, not so much, but Google docs is far easier to pull market data with, which takes priority for my purpose. Unfortunately, I know absolutely nothing about Google Docs. I know that the task I am attempting to accomplish requires a script, but when I look at the scripts I have no idea what the language means. I wouldn't know how to execute/implement the script if someone were to write a fully functioning script to perform this function, much less how to edit a template that is roughly suited for the task.

I'm not really sure where to start, perhaps someone could aim me toward some documentation. Honestly, I don't even know what I don't know at this point.
Greygal
Redemption Road
Affirmative.
#2 - 2013-03-25 09:56:34 UTC  |  Edited by: Greygal
Scripting isn't necessarily required :) but you will get more flexibility using scripting, of course. There is a ton of information out there already on scripting in Google Docs, much more than I can get into here.

The built-in conditional formatting is limited to the cell you are using it in - you cannot format one cell based on the value of another cell without the use of scripts.

There are some work-arounds, of course. Since you don't already have some knowledge of scripting, why not try something simpler by creating a column that does the test for you, then color the result of the cell containing testing formula using the built-in conditional formatting.

For example:

I want to know when the values in B are 90% or higher than the values in A:
1. I put this formula in C1:
=if(B1>(A1*.9), "MORE THAN 90", "NOT")

2. I then set the conditional formatting on C1 to be "Text Contains" and type "MORE THAN 90" in the box, and set the background to be green.

Obviously, for multiple value changes, you would need to nest several IF statements and set several conditional formatting rules in C1.

Ya, it's a bit of a cludge, but it doesn't require learning an entire new scripting language :)

*Edit: The formula above is very simplified and would not actually properly test for 90%... it's late and I'm brain damaged....but hopefully will give you an idea of how to work around this.

What you do for yourself dies with you, what you do for others is immortal.

Free weekly public roams & monthly NewBro new player roams!

Visit Redemption Road or join mailing list REDEMPTION ROAMS for information
Punx Etta
Brutor Tribe
Minmatar Republic
#3 - 2013-03-25 14:09:01 UTC
Greygal wrote:
Scripting isn't necessarily required :) but you will get more flexibility using scripting, of course. There is a ton of information out there already on scripting in Google Docs, much more than I can get into here.

The built-in conditional formatting is limited to the cell you are using it in - you cannot format one cell based on the value of another cell without the use of scripts.

There are some work-arounds, of course. Since you don't already have some knowledge of scripting, why not try something simpler by creating a column that does the test for you, then color the result of the cell containing testing formula using the built-in conditional formatting.

For example:

I want to know when the values in B are 90% or higher than the values in A:
1. I put this formula in C1:
=if(B1>(A1*.9), "MORE THAN 90", "NOT")

2. I then set the conditional formatting on C1 to be "Text Contains" and type "MORE THAN 90" in the box, and set the background to be green.

Obviously, for multiple value changes, you would need to nest several IF statements and set several conditional formatting rules in C1.

Ya, it's a bit of a cludge, but it doesn't require learning an entire new scripting language :)

*Edit: The formula above is very simplified and would not actually properly test for 90%... it's late and I'm brain damaged....but hopefully will give you an idea of how to work around this.


So kind of you to offer your help. There are definitely some helpful ideas here I hadn't thought of. Thanks.
Greygal
Redemption Road
Affirmative.
#4 - 2013-03-25 18:34:26 UTC
You're welcome, hope it helps!

What you do for yourself dies with you, what you do for others is immortal.

Free weekly public roams & monthly NewBro new player roams!

Visit Redemption Road or join mailing list REDEMPTION ROAMS for information
Punx Etta
Brutor Tribe
Minmatar Republic
#5 - 2013-03-25 19:00:47 UTC  |  Edited by: Punx Etta
Greygal wrote:
You're welcome, hope it helps!

It actually helped a lot. I set up the columns like you said, nested a bunch of different IF functions and simply used the formatted column like status lights. I actually like the way it turned out better than what I originally had in mind. I posted a screen below.

http://puu.sh/2nQcm

It performs its function fully, which is to guide me in my production choices at a glance. Thanks again!