Saturday, July 6, 2013

Google Spreadsheet Using Up and Down Arrow Keys in Cells

Question:

( By John )

Let's say i'm keeping track of an array of numbers. I want to add a column at the end that takes the variance between two cells, and adds an up or down arrow, similar to the stock exchange. Is that possible?

I've searched till my eyes bleed, but i'm not finding it. This document I don't want to change the color of the font. i had some help doing that earlier. but here i just want to add an up/down arrow depending on variance.

Up Arrow Key:



Down Arrow Key:

I can either make the png files, it'd be simple, or, more preferably, use a character in the spreadsheet arsenal.

Both webdings and wingdings have a suitable character for this, if i can figure out how to get it in there, i can use the script from my other sheet to color it. I could probably ask that same person for assistance, but i'd really rather not intrude on someone that's already helped me. I tried to search this myself but either it's not a common request (i thought it would be) or I'm simply searching the wrong keywords

Thoughts?

P.S. If I'm using the wrong location to post this question please let me know. been using Google webmaster tools to manage my domains forever, but I'm very new to spreadsheet management.

By the way, i can throw a pic up for an example of what i mean, but i think it's clear enough, just a colored character using webdings or wingdings, or a tiny image i'll make that appears in cell based on a formula. i would  use the same formula as i am now for the variance. i'm using ABS, example:

two columns total at the bottom,then this for a final cell that reports the variance. =ABS(D9-E9) i could use webdings and use: barb4up (0xE9) or barb4down (xEA) but frankly, I don't see much in the way of font face options in the simple tools for the spreadsheets



Solution:

Have a look at the following screenshot of my spreadsheet:




I have the following formula in Cell B4:


=if(A2=B2;"Both are equal";if(A2>B2;image("http://3.bp.blogspot.com/-ZsTNwUp2IAs/UdfCCn6UMwI/AAAAAAAABMU/pDonyN-Ijnk/s1600/up.bmp");image("http://1.bp.blogspot.com/-IwAMIDRHhkc/UdfCCoVEIlI/AAAAAAAABMY/uyEdQ1M2uGk/s1600/down.bmp")))

And the following formula in Cell B5:

=if(A2=B2;"Both are equal";if(A2<B2;image("http://3.bp.blogspot.com/-ZsTNwUp2IAs/UdfCCn6UMwI/AAAAAAAABMU/pDonyN-Ijnk/s1600/up.bmp");image("http://1.bp.blogspot.com/-IwAMIDRHhkc/UdfCCoVEIlI/AAAAAAAABMY/uyEdQ1M2uGk/s1600/down.bmp")))

So now, if value of A2 is greater than B2, then B4 will show image of "Up Arrow" key.


And if value of B2 is greater than A2, then B5 will show image of "Up Arrow" key.

So now you can take the idea from this blog post and implement it to your desired requirement.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

1 comment:

  1. Wanted to follow up here, this works perfectly!

    I uploaded a screenshot. this will be used on a different worksheet, but since i was already working in that one, i put it there for testing.
    Not sure how to show a pic so i'll try a regular HTML markup version, and a direct link

    http://i.imgur.com/fDzOhlW.jpg
    [IMG]http://i.imgur.com/fDzOhlW.jpg[/IMG]

    Thank you VERY much

    ReplyDelete