I want to show values to months on a chart. However I desire to show the last six weeks separately on the same chart. How could it be possible? Any help would be great.
Posts by onexc


Hello,
I use this array formula to reveal sheetname if lookup value found in one of the sheets.
Code{=IFERROR(INDEX(SheetList,MATCH(1,(COUNTIF(INDIRECT("'"&SheetList&"'!$A:$A"),A4)>0),0)),"0")}
The problem is that, This formula finds and displays the sheet name where first occurrence of value found. For instance, value A150 is found in Sheet2. However, the same value also exists in the Sheet3. I also want to display other sheet name where lookup value found. Any advice would be great.

I am retrieving values from several different workbooks and their sheets with index formula instead of indirect and address because of closed workbooks. However, file names change every month (for instance; bla_bla_Jan.xlsx to bla_bla_Feb.xlsx). If I break the links and change them every month, does it affect current values gathered with index formula? Or is there any other flexible method to lookup workbook names and sheets by month names only while keeping current values? Thanks in advance.

Thanks. It works like a charm.

I am trying to adopt a code to find special characters in a string. Macro works if cell contains only one character in the code, but does not work if it is with other characters which are not in the code.
For instance, I am looking up semicolon sign anywhere in the string. If cell contains only one semicolon, it returns false as expected. However, if cell contains
it returns true as not expected. How can I arrange it?

Perhaps this:
=OR(COUNT(SEARCH({",",";","^","(",")","&"},LEFT(A1,FIND("@",A1)1)))>0,ISERROR(SEARCH(".",RIGHT(A1,LEN(A1)FIND("@",A1)))))
Thank you very much. It works as expected.

Here's a way using Data Validation. You may be able to adapt the formula to use in a sheet.
Thank you very much.

I am trying to find out wrong email addresses by searching multiple characters. Formulas I applied work separately but I could not find a way to combine them to apply in a single cell. Is there shorter way or can you show me how to combine those.

This is a case of getting the maths clear..
For example ...
Lets assume the selling price = 1.00 (or 100%) and the selling price = all costs and all commissions and the desired profit.
If thew desired profit = 25 cents in every dollar then the assumption is that costs and commissions are 75 cents and the markup % is 33%.
Are you able to calculate the total of costs and commissions THEN add your desired markup?
Of course ..if the commissions are based on the end selling price it is a little more complicated but the same rationalse applies and it may be necessary (but not good practice) to enable iterative calculations (Options / Formulas).
From a maths (algebra) perspective you should be able to apply the maths in either direction.
Let me know if I can help further.
Thank you. I will make a calculation.

A sample worksheet would help to get a solution.
Added. Thank you very much. Normally, I can do this only for one commission rate: ( Costs Total * Commission Percentage ) / ( 1  Commission Percentage ) gives me what I want to. However, in this case, same commission calculations are connected to each other.

Hello all and happy holidays,
I am trying to calculate the final sales price for Etsy sales over a desired profit price, but couldn't handle it. The problem is that commission rates are connected to each other and one changes, others change too. I could solve the problem when only one commission rate applied, but multiple commission rates are getting hard to get out of. Is there a magic way that when I enter purchase price from the supplier and shipping fee as costs (only those two), and the profit I want to have, populate all other fields and finally get the price that I should sell? I hope it is not complicated. Thank you.

Great ...
So a simple solution is to create a Custom Format ... say 00,00
And then use formula :
Hope this will help
I AM CRYING OF HAPIINESS :') THANK YOU!

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.

Hello,
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.

Hi,
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 interdependent ...
5. Why the event macro clears the dependentproduct 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.

Delighted ...
Thanks for your Thanks ... AND for the Like
Hello Carim,
Could you check the workbook what I did myself. It returns an error.

Attached is your test file ... with the Basic Principle of Dependent DropDown Lists ...
Hope this will help
Hi Carim,
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 reallife 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.