CELL FORMAT - IF CONDITION

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows
My Cell Format is ""0.00" Cr". But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other programme. Now, I want to get the value from this cell to another cell based on “Cr” by using IF condition. But “Cr” is not appeared in the cell for applying formula. Can anybody help me?

Thanks
Suresh
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
If you use office 365 then filter option do recognize Custom formatting and we can filter out Dr and Cr
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
But the probelm with CSV file is when it is saved it removes all the formula and Additional sheet.

You are saving it as a CSV file first, changing it to text and reopening it. Once it is reopened then you save it as an Excel file as you have already changed the column to text so no reason to save it to CSV again.
It is a problem if they have other formula before the first save.

If you use office 365 then filter option do recognize Custom formatting and we can filter out Dr and Cr
Good option if the OP had 365 but their profile states they have 2007.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
You are saving it as a CSV file first, changing it to text and reopening it. Once it is reopened then you save it as an Excel file as you have already changed the column to text so no reason to save it to CSV again.


That is true but what happens is that there are numerous Parent and sub ledgers. Parent ledgers are formatted in a particular manner and there are subtotal total etc etc. If we remove the formatting it will be very diffcult for the user to recognise parent and sub ledger. This type of report is generated by an accounting software. So what i do is i normally go with CSV file and paste the formated column and use it as helper.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

@CA_Punit If that is the case with the OP's data (nothing to tell me by what they posted that it is yet) then I think your method is going to be the only way around it with the OP's version using a formula solution.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
I have tried various other ways but this method is time-saving and user friendly.
Also i know the formatting details as i am also an user of the same accounting package.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

as i am also an user of the same accounting package
I assume that you can tell that by the image?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
Yes... Mark.. Suffered a lot when i was a trainee almost 2-3 years back. I have done it manually for almost 1000-2000 entries
 

loginid

New Member
Joined
Jun 5, 2015
Messages
8
@MARK858
Cr is short form of "Crore" that is 10 million.

Now I guess the person wants the value in Crores in a single cell while doing a billing or invoicing job.

That is best achieved by custom formatting I think.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,602
Messages
5,523,831
Members
409,539
Latest member
Re1ease

This Week's Hot Topics

Top