A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Conditional formatting based on another column



 
 
Thread Tools Display Modes
  #1  
Old April 20th 08, 10:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Conditional formatting based on another column

Hello all. I am using Excel 2004 for Mac to create a directory index
of a hard drive with complete hierarchy, file sizes, & file kind for a
large video project. Something that might be done more easily and
flexibly in an outline processor but for the sake of portability, I'm
doing it in Excel.

I currently have 2 columns for the file size, one column for the
numbers and one for the units, (GB, MB, KB). This column has
conditional formatting applied to change the color of text based on
what unit it is, which works great but only on this column. I would
prefer that same formatting to apply to the number column as well so
that "48 KB" & "48 GB" each have heir own clear distinguishing color.

I can't figure out how to properly write the formulas for the entire
column so that the text color of any given cell will change based upon
the cell to the immediate right.

Here is a tiny sample of some I've tried, tell me what simple thing
I'm doing wrong.

=$V"GB"
=$V=GB
=$V="GB"
=$V=GB
=IF($V=GB)

I know the syntax should basically say Column V is "GB" then apply
this formatting, etc for each formatting rule.

Also, it would be nice to have the spreadsheet properly add the total
file size of all the listed files & return the sum at the bottom. 5 GB
+ 5 MB + 5 KB does not equal 15... I know that would involve some
conversion or adjustment of decimal points but I'd like the data
display to stay simple 1 MB, not 1,024 KB & 1 GB not 1,048,576 KB. Any
help with either of those would be greatly appreciated.
Ads
  #2  
Old April 20th 08, 11:22 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Conditional formatting based on another column

For the CF, select column V and Format>CF>Condition1>Formula is:

=$W1="GB" format the font color or background color.

Add two more conditions for MB and KB

To sum like items..............

=SUMIF(W1:W100,"=GB",V1:V100) & "GB"

Likewise for "MB" and "KB"


Gord Dibben MS Excel MVP


On Sun, 20 Apr 2008 14:52:31 -0700 (PDT), wrote:

>Hello all. I am using Excel 2004 for Mac to create a directory index
>of a hard drive with complete hierarchy, file sizes, & file kind for a
>large video project. Something that might be done more easily and
>flexibly in an outline processor but for the sake of portability, I'm
>doing it in Excel.
>
>I currently have 2 columns for the file size, one column for the
>numbers and one for the units, (GB, MB, KB). This column has
>conditional formatting applied to change the color of text based on
>what unit it is, which works great but only on this column. I would
>prefer that same formatting to apply to the number column as well so
>that "48 KB" & "48 GB" each have heir own clear distinguishing color.
>
>I can't figure out how to properly write the formulas for the entire
>column so that the text color of any given cell will change based upon
>the cell to the immediate right.
>
>Here is a tiny sample of some I've tried, tell me what simple thing
>I'm doing wrong.
>
>=$V"GB"
>=$V=GB
>=$V="GB"
>=$V=GB
>=IF($V=GB)
>
>I know the syntax should basically say Column V is "GB" then apply
>this formatting, etc for each formatting rule.
>
>Also, it would be nice to have the spreadsheet properly add the total
>file size of all the listed files & return the sum at the bottom. 5 GB
>+ 5 MB + 5 KB does not equal 15... I know that would involve some
>conversion or adjustment of decimal points but I'd like the data
>display to stay simple 1 MB, not 1,024 KB & 1 GB not 1,048,576 KB. Any
>help with either of those would be greatly appreciated.


  #3  
Old April 21st 08, 05:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Conditional formatting based on another column

On Apr 20, 6:22*pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> For the CF, select column V and Format>CF>Condition1>Formula is:
>
> =$W1="GB" * format the font color or background color.
>
> Add two more conditions for MB and KB
>
> To sum like items..............
>
> =SUMIF(W1:W100,"=GB",V1:V100) & "GB"
>
> Likewise for "MB" and "KB"
>
> Gord Dibben *MS Excel MVP
>
> On Sun, 20 Apr 2008 14:52:31 -0700 (PDT), wrote:
> >Hello all. I am using Excel 2004 for Mac to create a directory index
> >of a hard drive with complete hierarchy, file sizes, & file kind for a
> >large video project. Something that might be done more easily and
> >flexibly in an outline processor but for the sake of portability, I'm
> >doing it in Excel.

>
> >I currently have 2 columns for the file size, one column for the
> >numbers and one for the units, (GB, MB, KB). This column has
> >conditional formatting applied to change the color of text based on
> >what unit it is, which works great but only on this column. I would
> >prefer that same formatting to apply to the number column as well so
> >that "48 KB" & "48 GB" each have heir own clear distinguishing color.

>
> >I can't figure out how to properly write the formulas for the entire
> >column so that the text color of any given cell will change based upon
> >the cell to the immediate right.

>
> >Here is a tiny sample of some I've tried, tell me what simple thing
> >I'm doing wrong.

>
> >=$V"GB"
> >=$V=GB
> >=$V="GB"
> >=$V=GB
> >=IF($V=GB)

>
> >I know the syntax should basically say Column V is "GB" then apply
> >this formatting, etc for each formatting rule.

>
> >Also, it would be nice to have the spreadsheet properly add the total
> >file size of all the listed files & return the sum at the bottom. 5 GB
> >+ 5 MB + 5 KB does not equal 15... I know that would involve some
> >conversion or adjustment of decimal points but I'd like the data
> >display to stay simple 1 MB, not 1,024 KB & 1 GB not 1,048,576 KB. Any
> >help with either of those would be greatly appreciated.



Thanks! The CF formulas work perfectly, I just had to change the
column from W to V. The like items sum is fine for now, but ultimately
I'd want a combined total where it rounds up to the next unit after it
passes 1024. 1023 KB + 513 KB will round to 1.5 MB and so on. I'll try
to figure it out later this is a great jump start for a relative
formula newbie!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting in pivot tables based on another column Dave Excel Discussion (Misc queries) 4 January 23rd 08 11:01 PM
Conditional formatting based on another column. Rob White Excel Discussion (Misc queries) 4 April 24th 07 05:25 PM
Conditional Formatting based on entire column mike Excel Worksheet Functions 7 February 6th 07 10:40 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional formatting based on column snax500 Excel Discussion (Misc queries) 4 April 27th 05 06:13 PM


All times are GMT +1. The time now is 02:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.