if statement based on custom number format of a cell

[anandvh]

Hi,

My accounting package exports data into excel. The numbers are all formatted either as ""0.00" Cr" or ""0.00" Dr" (accounting equivalent to plus and minus).

I am trying to format reports in Excel using the data exported by the accounting package. If the cell number format is ""0.00" Cr" in the exported data, I want the number to be displayed as (100.00) in my report. Similarly, if the cell number format is ""0.00" Dr" in the exported data, I want the number to be displayed as 100.00.

I have been trying to find a way of using an IF statement to achieve this - IF("Custom Number Format of Cell A7" = ""0.00" Cr", -A7, A7), but not able to get the result.

I would be very grateful if someone can guide me on how this can be achieved.

[InvisibleMan]

Suggest you post a sample workbook with some typical data.

[D.Lovell]

The CELL function can return a code that corresponds to the number format of the cell...

Please Login or Register  to view this content.

Maybe you could use that in your logic argument if the cells are actually formatted that way, or perhaps in conjunction with conditional formatting.

If the data is just printed that way and the cells are still formatted as general or text or whatever, then you maybe better off extracting the figure more along the lines of...

Please Login or Register  to view this content.

Or without the "" " " speech marks...

Please Login or Register  to view this content.

I don't know for sure without seeing it, I suspect I could be more helpful if you posted an example.

[anandvh]

Hi,

My accounting package exports data into excel. The numbers are all formatted either as ""0.00" Cr" or ""0.00" Dr" (accounting equivalent to plus and minus).

I am trying to format reports in Excel using the data exported by the accounting package. If the cell number format is ""0.00" Cr" in the exported data,
I want the number to be displayed as (100.00) in my report. Similarly, if the cell number format is ""0.00" Dr" in the exported data, I want the number
to be displayed as 100.00.

I have been trying to find a way of using an IF statement to achieve this, for example IF("Custom Number Format of Cell A7" = ""0.00" Cr", -A7, A7), but not able to
get the result.

I have attached a sample workbook that will provide more clarity.

I would be very grateful if someone can guide me on how I can be achieved the desired result. Thanks in advance to everyone for your efforts in helping me

Anandvh

[JohnTopley]

Sample VBA

Please Login or Register  to view this content.

Named ranges CR and DR formatted as your input.

Macro changes format.