Last 50 Posts on Chandoo.org

Shashi Kumar on 2020-09-21 06:29:40
Hi sir you are providing really awesome things. Can you provide a sheet for payment outstanding amount alert of customer with days or credit days allowed to the customer so that we can follow up on time for the payment. Thanks

Hui... on 2020-09-20 15:07:12
@York Have a read of : https://social.msdn.microsoft.com/Forums/office/en-US/aa1ae70a-4a70-467f-afea-318de2281f71/using-vba-to-capture-word-documents-and-paste-to-powerpoint?forum=officegeneral

York W. Porter on 2020-09-17 20:47:14
Great job here...What is frustrating to me is that there is no way, as far as I know, to do the same with Word documents. Word has so many options (forms, etc.) and it seems that one should be able to do the same with Word. I know that Word headings can be automatically converted but not images, forms, etc. (as far as I know). Is anyone aware of any VBA routine that will allow us folks that like to use Word in document generation to do a similar thing that has been demonstrated here???? Thanks!!!

Cory Hendrix on 2020-09-16 20:15:56
Hello, I am looking for a template that uses the Bing Maps MIO API. I have your distance and duration template, which is awesome, but I am trying to get something that uses MIO to perform route optimization. Please help if you can. Thanks in advance.

Christos on 2020-09-16 06:23:20
I am working in Excel 2013 and have the following problem : I have two columns A and B. Column A is populated with negative and positive numerical values while Column B is populated with positive-only numerical values. I want Excel to find in Column A the first value (from top to bottom) that is greater than zero (i.e. positive) and return the value of the corresponding cell in Column B. I have tried =INDEX(ColumnA;MATCH(TRUE;ColumnA>0;0)) but it gives me #Value! Please help me. Thanks a lot.

Sandeep kumar on 2020-09-15 17:26:51
Hi sir can u please tell me how can we calculate the journey speed between two places with in the city using the google map APIs

Chandoo on 2020-09-15 02:59:15
I already posted this video on my YouTube channel Daniel. Check it out here https://www.youtube.com/watch?v=9eR8NMC8Vq0 I will update the post during weekend to reflect PQ method.

Daniel Lamarche on 2020-09-15 02:25:27
Please Calendar to Table using PQ. I am convince that it would be simpler!!!! Thanks Chandoo

CA Nirmal Choudhary on 2020-09-14 08:38:17
Now we can do by formula SMALL and SUM SUM(SMALL(RANGE, {1, 2,3}))

Saurav on 2020-09-14 03:57:16
Hi Hui, Can you please guide me : I have created a data model in Excel using Power Query and Power Pivot. Now, i have used it in a Power BI using 'Import" feature feature. Now, once i refresh the old model in excel, how can i import the same in Power BI again keeping my visuals intact?

Vinay on 2020-09-11 06:25:17
Thanks Chandoo... I edited same in formula section and now its working fine, & Not to forget u have done a excellent script work with existing features of Excel.. Keep going with your contributions

Chandoo on 2020-09-10 20:23:43
Hi Ritu... This program tries to save the file in current workbook folder. So make sure you can actually save files there. Try by first saving the Excel file to a regular folder like desktop or mydocuments. Let me know if you still have a problem.

Chandoo on 2020-09-10 20:22:26
Hey Vinay... Just edit the named ranges "list1" and "list2" and point them to the 100+ cell ranges.

Chandoo on 2020-09-10 20:21:09
Hey Johnny... Not sure why the chart wouldn't work with XLSB. I suggest using a dynamic named range as source of the data. That way, you just need to adjust the named range formula inputs to update chart. Also, picture links are by default hyperlinked. So if you double click on an picture link, Excel takes you to target range. Let me know if you need more guidance.

Vinay on 2020-09-10 14:06:40
A quick demo of our comparison macro: --- this sheet has limitations wherein comparison is done only to data [16 cell] but doesn't go beyond that... could you please let me know how to edit cell range so as to accommodate 100+ cells [in both columns]

Ritu on 2020-09-08 19:05:12
Hey , Thanks for the code. The program is throwing an error when it reaches the line: Open filename For Output As #1 Please help.

Johnny C on 2020-09-08 14:17:09
Hi. Unfortunately it works when I open it but when I wwent to save as an XLSB it says some features weren't supported and went a bit nuts - I can't select the centre chart. I'm using Office 365 Pro Plus which is probably a bit behind. That aside, can you add some code (hence i used XLSB) to hyperlink to the central chart when you double click? If I can get that working I can get the series and parse the SERIES function to select the source data. I tried the worksheet BeforeDouble click event but due to the selection problem I couldn't get very far. I have code elsewhere that has a list of all worksheets in a giant scrollable userform and use the doubleclick event in that for the list to jump to the currently selected item in the list. My thinking is to create a range with linked pictures of the worksheets and worksheet name in carousel/Aero style so I can get to worksheets more quickly. It's a bit of work but I have worksheets with >100 different pages I constantly jump around and a slidable carousel would make navigating a lot easier. I would add a button to update the list and formulae. Cheers Johnny

Chandoo on 2020-09-06 21:43:14
Hi Ramona... You can use this measure to get the % of cost center sales output. =sum(Range[VALUE])/ calculate(sum(Range[VALUE]),all(Range[LinieBuget]), Range[TYPE]="SALES")

Ron MVP (2012-2018) on 2020-09-05 23:08:05
yes Here is an example article. google is your friend ... https://www.myexcelonline.com/blog/copy-and-paste-queries-across-workbooks-using-power-query/

Ramona on 2020-09-05 17:22:59
Hello, I have the same problem as Imran On columns i have months and i have different cost centers and I need excel to calculate the percentage of costs per each month, on the total sales of each cost center. Thank You!

Saurav Agrawal on 2020-09-05 05:36:41
Hi Chandoo, Is it possible to use a data model created in one workbook to use in another workbook?

Sumit Guha on 2020-09-05 00:08:26
Dear Sir, I am interested to join your online course of Power BI. How to get started for power bi course. Pls let me know the details for joining power bi course. Looking for your response. Thanking you. Sincerely, Sumit Guha.

Karthik on 2020-09-04 20:33:30
Hi, How do I change the data type of the graph data labels dynamically depending on the value of a particular cell. For instance, If the cell A1="abc" the data type in the graph should be percent, else, it should be number Thank you

Noah on 2020-09-04 02:00:27
Chandoo, I am just starting an Excel class, and everything in the class is new to me. I am learning how to use all of these great charts but don't know what they are all used for. Thank you for your post and I think I will be able to use this down the road throughout my business career

Noah on 2020-09-04 01:56:13
Thank you for this! I was just doing an assignment where I was having to replace words with other words in my Excel sheet. We are starting with the basics in my class, so I know I don't have a huge list that I would need to find and replace, but this is something that could be useful down the road for me!

slsuser on 2020-09-03 21:35:20
Interesting. How about converting the other way, convert columns into calendar format? Perhaps I have a list of people with dates, like vacations or sales and I want to count or sum and place the results into a calendar format to see how may people are off that day or the total sales for the day.

Daniel Dion on 2020-09-03 13:39:23
Here's another formula using one of your trick - https://chandoo.org/wp/extract-words-with-filterxml/ =SORT(CHOOSE({1,2},FILTERXML("" & SUBSTITUTE(TEXTJOIN("|",,FILTER(TRANSPOSE(data),{1,0,1,0,1,0,1,0,1,0,1,0})),"|","") & "", "//n"),FILTERXML("" & SUBSTITUTE(TEXTJOIN("|",,FILTER(TRANSPOSE(data),{0,1,0,1,0,1,0,1,0,1,0,1})),"|","") & "", "//n")),1)

Brian Sizemore on 2020-09-03 01:24:19
This competition is very cool. I love to see your pros and cons feedback on these dashboards. Thank you for taking the time to do that. Delivering intelligence is much more than creating graphs... there is a thoughtfulness required that is touched on by many of your comments! When is the next competition? Brian

yassine on 2020-09-02 18:25:54
1st question : you've created a new column to put the replacements in. how ca we replace in the original column without creating a new one ? 2nd question : how can we replace the value in the entire cell and not only the text (Using ReplaceValue instead of text.Replace) ? Thanks you in advance

Chandoo on 2020-09-02 02:15:53
Good suggestions Ron. I have included a snapshot of how column profiles look to the article. I will revise the mind-map when I release another update to this page. Thanks also for sharing the image of relationship diagram.