# Conditional Formatting

Microsoft Excel 2003 (full product)
September 29, 2009 at 11:39:33
Specs: Windows XP
 I want to know a simple way of doing the following:When a user enters a value in one cell, the subsequent cells can be formatted a certain way. I would like to do this for measurements, such that, a person can enter in values in square meters and the values can be converted to square feet.The user can also choose to enter the values in square feet, and thus, no change would need to be applied.Finally a person can enter the values in tsubos and a conversion to square feet can take place.I envisioned a system where users could enter 1 for square feet, 2 for square meters and 3 for tsubo. Then from those values, cells could then use the appropriate conversion method necessary to get them to square feet. I don't want to use macros as we have many users and not all are using the same version of Excel, thereby causing issues with launching.

See More: Conditional Formatting

#1
September 29, 2009 at 12:12:54
 There is a =CONVERT() function in the Add-in Analysis Tool Pack check to see if you have it installed.Tool BarToolsAdd-insIt is used like:=CONVERT(number,from_unit,to_unit)So for meters to feet it would be=CONVERT(A1,"ft","m")See the HELP files for a full list of possible conversions because the unit names and prefixes are case-sensitive.MIKEhttp://www.skeptic.com/

Report •

#2
September 29, 2009 at 12:15:41
 What's a "tsubo"?You could create a drop down where the user would choose his unit of measure and have the conversion cell base its conversion method on whatever is in that cell.I do not expect this formula to work...it's just a concept:With your drop down in A1, and the input values in A2, put something like this in the cell where you want the final result:=IF(A1="Feet",A2,IF(A1="Meters",CONVERT(A2,"cm","in"),IF(A1="Tsubo",CONVERT(A2,"ts","in"))))

Report •

#3
September 29, 2009 at 12:21:40
 Hi,How about putting your three measurement systems into a drop down list in one cell (using data validation).Use the measurement names - no need to use 1,2 or 3.The user selects the measurement system they are going to use.The value (in the selected measurement system) is entered in a second cell.In the third 'result' cell there is a nested IF function that decides which conversion formula to use based on the name in the drop-down cell.e.g.,Drop down in A1, data entry in A2 and in A3: =IF(A1="Square feet", A2,IF(A1= etc.Regards

Report •

Related Solutions

#4
September 29, 2009 at 12:25:56
 Derby Dad, it did work! A Tsubo is a unit of measurement in Japan equal to 35.5 square feet. It is similar to the Tawainese Ping. I didn't know anything about these things until I started here.Thank you Mike for the Analysis ToolPak tip. I didn't know about that either. Humar, I haven't tried yours yet, and I will, because the more I know, the better. Thanks all!

Report •

#5
September 29, 2009 at 12:33:03
 Hi,The solution I suggested is the same as DerbyDad03's, so please don't waste time on looking at mine.Regards

Report •

#6
September 29, 2009 at 12:33:36
 Ok, Humar's tip ultimately worked the best as Excel does not have a mechanism for converting Tsubo to Square Feet. So I tweeked the conditional formula a bit to end up with this:=IF(A1="square feet",A2,IF(A1="square meters",CONVERT(A2,"m","ft"),IF(A1="tsubo",A2*"35.5")))In all, I used all three of your answers! Thank you guys a lot

Report •

#7
September 29, 2009 at 12:38:05
 re: ...it did work! No need to be nice, 'cuz we both know it doesn't work.The concept will work, but the actual formula that I supplied will not.

Report •

#8
September 29, 2009 at 12:43:34
 No it did. That formula I put into the function worked! Of course, not for the Tsubo portion, but it worked for anything that was square feet or square meters. I just tweeked the end to get it to work for tsubo.Just out of curiosity (or not), how many arguments can the If function take? Also, can vlookups work on Drop Down menu inputs...

Report •

#9
September 29, 2009 at 12:48:53
 Just a few tips...1 - This is not "conditional formatting". Conditional Formatting is a specific feature found under the Format menu that will allows for the formatting of cells based on specific conditions.Converting units of measure is not the same thing as formatting a cell. Bold, Fill Colors, Font Colors, etc. are examples of what can be done with Conditional Formatting.2 - What you called a "conditional formula" is more commonly known as an "IF statement", more specifically in this case, a "Nested IF".3 - You shouldn't use double-quotes around a number in a formula. The double-quotes tells Excel that whatever is in between the quotes is text. In this case, because you are performing a mathematical operation on a numerical text value, Excel will convert the text to a number first, but that is inefficient and could cause unwanted results in certain circumstances.

Report •

#10
September 29, 2009 at 12:54:51
 re: No it did. That formula I put into the function worked! Of course, not for the Tsubo portionA formula that only works in certain situations doesn't work.re: how many arguments can the If function takeThe IF function takes 3 arguments:logical_testvalue_if_truevalue_if_falseIf what you really want to know is "How many nested IF's can there be?", the standard answer is 7, although there are workarounds. DAGS Excel 7 Nested If Limit for more info.re: can vlookups work on Drop Down menu inputs?Try it and see.

Report •

#11
September 29, 2009 at 13:02:03
 Hi,The limit on nesting IF's is I think the same as nesting any functions in a formula.In Excel 2003, the limit is 7 levels.I reached it once in a formula to calculate the day of the week for every day since 01 Jan 1900, using the formulas for calculating leap years.(Excel itself doesn't return the right days before 01 March 1900)You can get round the nesting limit by nesting part of the formula in one cell and then referencing that cell in the main formula.Excel 2007 allows a lot more levels of nesting.As to Vlookup, if you mean can Vlookup use the result in the drop down cell as the value to be looked up in a range, then Yes it can be.Regards

Report •

#12
September 29, 2009 at 13:05:50
 I guess I've been using Conditional Formatting in the wrong way all these years. Shame on me.I also found a way to use VlookUp to reference an entry from a drop down cell. Thanks again guys.

Report •

#13
September 29, 2009 at 13:22:11
 Ahh!Now I understand why I was confused about one of your previous posts - I thought that when you said 'conditional formatting' you meant the process of formatting a cell (e.g. Bold or colored), based on a formula or its value, when all the time you were talking about using IFs.Regards

Report •

#14
September 29, 2009 at 14:30:30
 Just one more thing, in your original post you asked:enter in values in square meters and the values can be converted to square feet.For square's you need to square the formula:=CONVERT(CONVERT(A1,"ft","m"),"ft","m")MIKEhttp://www.skeptic.com/

Report •