Posts by onexc

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.

Hello,

I use this array formula to reveal sheetname if lookup value found in one of the sheets.

{=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

abcd;

it returns true as not expected. How can I arrange it?

Function CharFinder(str As String) As Boolean
For I = 1 To Len(str)
 ch = Mid(str, I, 1)
 Select Case ch
  Case ",", ";", ".", "#", "*"
   CharFinder = False
  Case Else
   CharFinder = True
  Exit For
 End Select
Next
End Function

Quote from rory

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.

Quote from royUK

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

Validate Email Address

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.

=SUMPRODUCT(--ISNUMBER(SEARCH({",",";","^","(",")","&"},LEFT(A1,FIND("@",A1)-1))))>0
=SUMPRODUCT(--ISNUMBER(SEARCH(".",RIGHT(A1,LEN(A1)-FIND("@",A1)))))=0

Quote from r_hind

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.

Display More

Thank you. I will make a calculation.

Quote from alansidman

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.

Quote from Carim

Great ...

So a simple solution is to create a Custom Format ... say 00,00

And then use formula :

Code

 =A1*100

Hope this will help

Display More

I AM CRYING OF HAPIINESS :') THANK YOU!

Quote from Carim

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.

Quote from Carim

Hello,

When you say

Do your mean these ' Numbers ' are actually typed in as Text ...???

Display More

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.

Quote from bosco_yip

Maybe,

In I6 >> Data Validation >>

>> Allow : List

>> Source : =OFFSET($A$4,MATCH($H6,Company,0),2,,2)

Display More

Hello, bosco. It is useful solution. Let me apply the same way you do in my own sheet. Thank you very much.

Quote from Carim

" 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 ...

Display More

Thank you Carim. I am on case.

Quote from Carim

Delighted ...

Thanks for your Thanks ... AND for the Like

Hello Carim,

Could you check the workbook what I did myself. It returns an error.

Quote from Carim

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.

Quote from Carim

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.