As you know the decimal separator is set at the Application level ...
so the next question is the following :
Is your objective only a display issue ... or are we talking about numbers which should continue to act as actual numbers ???
Just a display matter. I just want to display as if they are separated by commas instead of dots in another sheet while keeping actual format as dots.
When you say
Do your mean these ' Numbers ' are actually typed in as Text ...???
Nope, Not at all, Carim. They are formatted as numbers that their decimals increased to four digits.
In my workbook, I have prices with four digits after dot like "25.000"; while considering this is formulated number format, in another sheet I want to display it as "25,000" with a comma instead of dot. If I change it through Options > Advanced > Decimal Separator, everything works like a charm but everything becomes to have changed! However, I just want to change it for numbers in a worksheet only. How can I use comma for numbers without breaking other dots? Thank you.
Hello, bosco. It is useful solution. Let me apply the same way you do in my own sheet. Thank you very much.
" It returns an error ..."
Because you have not followed the structure of the Test File ...
You should take some time to analyze the Test File in order to fully understand :
1. Why is there a second worksheet with all the references ...
2. Why have all the validation lists have been named ( with Ctrl F3 ... you can see the list)
3. Why the data validation lists are using these specific range names ...
4. How are the two dropdown lists becoming inter-dependent ...
5. Why the event macro clears the dependent-product cell ... while waiting for the selection of the company ...
The ' dependency link ' hinges on two elements : the way the ranges are named ...AND... the use of the INDIRECT function
Once, you take the time to demystify the process ...everything becomes clear and simple ...
Thank you Carim. I am on case.
Attached is your test file ... with the Basic Principle of Dependent DropDown Lists ...
Hope this will help
Thank you very much and much again. It works like a charm. You gave me what I needed. Case is solved.
Quite honestly the sample file you have attached is way too vague to come up with any meaningful recommendation ...
Could you share a file which is closer to your real-life situation ...
Hello Carim, here is the file you requested. I tried to adapt some examples from different websites but none of them worked for me. Sample workbook is in the attachment and very simplified as real life situation.
Just had a quick look at your workbook ...
you will have to accept to redesign its basic structure ... not only to be in a position to easily build your dependent dropdown lists
... but also to avoid sowing the seeds of all your future problems ...
Okay. Of course, little tweaks are acceptable.
I'M CRYING OF HAPPINESS NOW
I have a workbook, and trying to arrange a dependent drop down list to product name and company. Problem is that, product names (named ranges or groups? I don't know how I should call them) are placed in different sheets in the same workbook and they have multiple words. Addition to this, in the main sheet, product company and product name are placed in different cells. After a couple of hours, my work returned no success. I do need help. Thank you. Sample is in the attachment.
In order to create your Product List from many rows where cells contain your Comma-Separated products ...
Have re-arranged your test file and added a macro to Create your List ... which you can, then, use as a Data Validation ...
With this worksheet configuration, you can expand as much as needed the Product List in Column A
Attached is your V3 file
Hope this will help
Thank you sooo much Carim, Wonderful! You made my day again. I'm crying of happiness now
Quite a surprising set-up ...
Attached is your test file
Hope this will help
Wow, That is incredible Carim I cannot thank you enough. All seems perfect. Just one more question... Normally, there are too many categories more than this sample workbook; is there a practical method to pull out each product? I tried this method: Text to columns then paste as transpose but it will take ages.
I would like to list my product by using of a dropdown. Then, when a product selected in a cell via dropdown, its fee rate percentage must have appeared. However, I don't know how to overcome this. Because products have been categorized and each category is in a cell as seperated by a comma. I worked on a partial match solution but it didn't work. Any help would be great. For some reasons, I do not want to break those categories, though helper can be okay if applicable. Or any VBA solution at the last analysis. Thanks. File is in the attachment.
Fluff is right ....
In addition ... Excel is designed to make your life a lot easier .... see your test file attached
Ah Yes. I don't know how I missed that one. Thank you very much you, too, Carim.
although I think the result should be 16 not 46
Hello Fluff13. Thank you very much. This is what I am looking for. Yeah, that is correct. It was 16. Thanks again.
I am trying to formulate to find out lookup a value to two criterias. One of them is number (0,1,2... etc as unit of weight). And the other one is gathered from numbers again but headers stored as text (company names). Desired result is included in the Excel Workbook here. Thanks in advance.
Formula is: Sell Price = Cost / (1- Margin %). In your example, 24.9/(1-. 85) will give you a selling price of 166.
Thank you Rareskyfive, this one is also what I was exactly looking for. You just made my day.