• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in Data Sorting using Active X Control

O

Om Dimri

New Member
Hi,

I am trying to use conditional formatting along with Text Box(Active X) control on a spreadsheet to find out duplicate values for entered text. The data is downloaded from SAP however when it gets downloaded generally the numbers are either in Text or General format and after putting conditional formatting, it will not show up until I double click on the cell.

After double clicking the cell, the format changes from General to number(i figured out this as aligment of the number changes) and then the conditional formatting works.

Looking forward for some help as the data downloaded from SAP is generally 50,000 to 60,000 records and double clicking every cell will be time consuming as well as not efficient.

Thanks & Regards,
Om Dimri
 
I think the easier option would be to treat the Text Box entry as TEXT. This could be done through the VB, or with the TEXT function, depending on where you want to control it. The latter in a CF rule would be like:
=A2=TEXT(MyCell,"@")

Alternatively, the method to quickly convert numbers stored as text is
  1. Find a blank cell, input the number 1
  2. Copy that cell
  3. Select range of text-numbers
  4. Paste Special, multiply
Done. The multiply method will only affect cells that actually contain numbers (not true text), so it's a fast, convenient method.
 
Thanks Luke. I tried that but to no avail. Problem still persists. I guess it is to do with data getting downloaded from SAP as when I try to set the format to number from CF option it shows some other number.
 
You must log in or register to reply here.
Back
Top