Search results

Ok, this is too cool to not share. Here is one more approach with formulas that works up to 10 characters. This one uses the BASE formula :) Given the text in I6, mapping table in A2:B27, and these additional ranges: Running numbers 1 to 10 in a range with first cell named as numStart ASCII...

@Chihiro... Thank you. It is a pleasure to have you here on Chandoo.org forum and learn from you. Keep up the good work :) :awesome:

@DashboardNovice There is a chance that I misunderstood your question. As there are no numbers in your workbook, I tried to guess what you wanted. So if you want to find out the sum of all Actual amounts for a particular employee in a given month end date (ie all pay periods in that month)...

I am not sure I understand this question properly. But the sort sure seems wrong. You can fix it by removing the reference to Report worksheet in your formulas. For example, Instead of this formula in Sales column (E) =IFERROR(INDEX(DeptData[SALES],MATCH(Report!A6,DeptData[Helper],0)),"")...

@Pranav Pawar Welcome to Chandoo.org forums and thanks for posting your question. Here is a formula that seems to work. As we don't have lots of data in your file (just 1 row of test data), I am not 100% sure. Try it and let us know. =IF(COUNTA(A2:K2)=11,"Rating 1", IF(AND(COUNTA(J2,K2)<2...

@M. Qayyum Anwar There is no option to show lines / bands on sparklines. The axis option may not work for you as you need 2 lines. One approach is to create 3 sets of sparklines and overlap them one on top of another to get the effect, using picture links. Something like this: See attached...

:awesome: formulas @Asheesh :) Here is one more, just for fun. Non array solution. =COUNTIFS($B$4:$B$7,G4,$C$4:$C$7,F4)>0

Please note that I do not appreciate this attitude. Next time you make a thread, please use correct titles. Mention a person's name only if that is the context of the thread (ex: Thank you Deepak for 2000 posts is a good title, Deepak, Please help my problem!!! will get you banned) Please read...

@Uzzal You have posted this question elsewhere in the same forum already. Why do you create more threads. Please note that addressing users in thread titles is not encouraged. If you wish to ask a particular user to help you, PM them. Also, please note that this is a public forum and everyone...

Interesting solution @Khalid NGO. @VDS as you have noted, using VBA is better in this case. You can use a simple UDF to achieve this task. See this code (add this to a module or to your personal macros workbook). Public Function convertText(thisText As String, mapping As Range) As String...

@Razorbec Welcome to Chandoo.org and thanks for posting your question. May be your column J is not truly blank. May be the values are 0 (often shown as blank) or empty spaces. I suggest posting sample data so that we can examine what is going on with your data.

@Vivek D You can do this in a couple of ways. If your users have Power Query too: Set up a query that reads speadsheet table to determine which rows to keep and use the value to filter the table in PQ. Then load the table to data model and access it in PP. For more instructions either play...

Hi Vinay, Welcome to Chandoo.org forums and thanks for posting your question. You can a macro to approximately allocate the jobs to employees. I say approximately because when using running total method, we may not find exact solution to match the % requirements. See the attached file. The...

@ThrottleWorks I have regenerated the forum stats. They should show correctly now. Usually it goes stale and gets fixed automatically.

Congrats @Deepak It is a pleasure to have you here and learn from you. wishing you many more ks..

Oops.. I thought the workbook was attached. Here you go.

@Abhishek Adhikari Welcome to Chandoo.org forums and thanks for posting your question. You can set up an extra column called "Actual Stock Quantity" and derive the quantity by looking at particular SKU, Stock qty (that you got from warehouse stock report) and how many orders are already...

Yes, that. I tested and it worked ok. What error are you getting?

@Temma - Welcome to Chandoo.org forums and thanks for posting a question. Try this code instead. Set SourceRange = ActiveSheet.Range(tableName & a) Set FillRange = Range(SourceRange.Cells(1, 1), SourceRange.End(xlDown).Offset(, 1)) SourceRange.AutoFill Destination:=rng2...

Welcome to Chandoo.org forums and thanks for posting your question. I am not sure why you are using such long and complex formula. You can use this instead. =IF(YEAR(H$6)=YEAR(D$6),INDEX($D$7:$D$9,MATCH(G7,$C$7:$C$9,0)),"") or even simpler...

Welcome to Chandoo.org forums and thanks for posting your first question. You are nearly there. Try this formula... =SUMIFS(CM15:CM29,$T15:$T29,"<="&CM$11,$U15:$U29,">="&CM$11) For more about date conditions like this read this article: http://chandoo.org/wp/2011/09/27/sum-between-2-dates/

Good solutions @Hui and @Khalid NGO Another one... =VLOOKUP("*" & LEFT(A2,FIND(" ",A2)-1), $A$10:$B$15, 2, FALSE)

Here is one more... http://chandoo.org/wp/2015/08/13/financial-analysis-modeling-concepts/ Scroll down and locate the Uber vs. Your car model. Listen to the podcast for details.

A tricky one... Essentially this is a longest winning streak problem. You can use this array formula in the pivot worksheet. =MAX(FREQUENCY(IF(B3:M3=1,$B$1:$M$1),IF(B3:M3=1,,$B$1:$M$1)))>=3 For more on how this formula works, read below 2 articles...

@shaikhrulez you are welcome :) I have turned this in to a contest at Chandoo.org. Let's see what our readers will come up with. http://chandoo.org/wp/2015/10/23/contest-visualize-kpi-data/