Posts by onexc


Thanks it works like a clock!

Hello
I created a table starting from a specific column number. I tried to sum cell values to the header type like Sales, Customer. For each one, I need to skip two columns to calculate the monthend grand total. I created this formula:
It does not work anyhow. Could you show me where I may be wrong? A sample file is included. Thank you.

Table headers are text, not date values. You could use something like:
=INDEX(MGSD,MATCH(Analysis!$A3,MGSD[Sales Code],1),MATCH(TEXT(B$1,"m/d/yyyy"),MGSD[#Headers],0))
You may have to alter the format code to suit your region if it doesn't use those codes.
Thank you very much Rory, it works like a charm!

Hello,
I am trying to retrive data with Index Match formula from a table in another sheet. Other sheet will include whole year date by date but it does not return any result. What am I missing here? A sample file included in.

Hello all,
My pivot table contains some values with different format. Because there are different data sets one below another. If I change the filter in pivot table, number format insterestingly does not change in pivot chart. How can I display different number formats in the same chart if source number format changes? For instance, If I filter A, it displays "K" for thousands, but If I filter B (it is percentage), it does not display as percentage in pivot chart. Any help would be great.

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