Modelling and Formatting

[Jimmy Tsawo]

I am currently undertaking an exercise at varsity. I have 3 columns, with base, target, and actual. these 3 columns can be in percentages, actual numbers, dates, or currency. For example, base is 1-Jan-2013; target is 31-Dec-2013. The actual date is 20-Dec-2013.



I would like the second and third columns to automatically change to the format of the first column, for example, if the first column is in percentage (%), the remaining two columns automatically change to percentage.

is there a way of creating an error message on the second and third column if they do not match the format of the first column?

Thank you in advance those who can assist.

[macropod]

A formula cannot change a cell's format; it can only change the way data are displayed. And, although you could set up a complicated IF test containing the CELL("format",reference) function to test a cell's format, to obtain basic information such as date, percentage, currency, etc., it can only provide limited information about how many decimal places are displayed, whether thousands separators are used, how a date is formatted, etc.

[Jimmy Tsawo]

I have used the following formula and it worked very well.

=AND(CELL("format",A1)=CELL("format",B1),CELL("for mat",A1)=CELL("format",C1))

My next question is as follows:
I want to add another column with a dropdown of the different units of measurement, eg %, dates, numbers, etc.

How can I use this formula to detect different formats in all the columns?

[macropod]

Your dropdown would need a lookup table with both the CELL format codes and their descriptions. Look up Excel's Help file for the CELL function to see what those are. I'm not sure how you'd plan to use the dropdown, though.

To return the codes for a positive match you could use:
=IF(AND(CELL("format",A1)=CELL("format",B1),CELL(" format",A1)=CELL("format",C1))=TRUE,CELL("format", A1),FALSE)