Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting based on another column
On Apr 20, 6:22*pm, Gord Dibben <gorddibbATshawDOTca wrote:
For the CF, select column V and FormatCFCondition1Formula 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting in pivot tables based on another column | Excel Discussion (Misc queries) | |||
Conditional formatting based on another column. | Excel Discussion (Misc queries) | |||
Conditional Formatting based on entire column | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional formatting based on column | Excel Discussion (Misc queries) |