Last 50 Posts on Chandoo.org

kris on 2020-12-03 10:33:39
Hi Chandoo, This is perfect - One quick question, How can I add a hyperlink to the risks - So that I can click on the particular risk and it takes me to the actual row of that item. Many thanks in advance.

Passionista - Tutku on 2020-12-01 10:19:48
"NO NEED FOR FILLING THE MISSING QUANTITY" I realized that no need for neither filling the missing amount nor overwhelming formulas. I used INDEX and MATCH and utilized approximate match. Here is my much shorter and subtle formula for the first cell: =INDEX(Pricing[Unit price];MATCH(0;IF(Pricing[Product]C6;"_";--(D6> Pricing[Max. Qty]));-1)) I hope this helps!

Emre on 2020-12-01 08:48:56
Nad's Formula without filling the min quantity column: =IFERROR(VLOOKUP($D6,IF((Pricing[Product]=$C6)*(Pricing[Max. Qty]>=$D6),Pricing[[Min. Qty]:[Unit price]],""),3,1),MIN(IF(Pricing[Product]=$C6,Pricing[Unit price],""))) $D6 for quantity $C6 for product name

wordpress on 2020-11-30 04:34:57
This is my first time visit at here and i am actually impressed to read all at single place.

Usman Khurshid on 2020-11-29 19:17:36
May I please get the solution to the answers of the SUMIFS sample file. There is a homework section which I am not able to solve.

Keith A Breedlove on 2020-11-26 19:22:32
Is there any way to do partial or fuzzy matching? For example, I live in Florida and I want to compare 2 lists of cities and I know that most times Tampa is spelled correctly, but I have also see Tmapa or Tampz. There is also the case where the length of the data may be different, as when one list only has the street address but the second list may have a street address that includes "Apt 20" or "Ste A"

David on 2020-11-25 00:23:09
I have a spreadsheet with two worksheets, each with a number of saved views for different ways of presenting the sheet to the user. These are selected by short macros driven from buttons on the sheet. I'd like to have these saved views as the basis of a print area that is dynamic depending on the last filled row. Any thoughts?

Joel Castaneda on 2020-11-24 23:32:18
Hello Chandoo, I've been searching for a solution like the one presented on "Filter on any column" VBA trick. I have followed all steps, array formula w the &" " at the end My code Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("search_string") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then FastFilter (KeyCells.Value) End If End Sub Module Sub FastFilter(sch As String) Dim lo As ListObject Set lo = ActiveSheet.ListObjects(1) lastcol = lo.ListColumns.Count If lo.AutoFilter.FilterMode Then lo.AutoFilter.ShowAllData lo.Range.AutoFilter Field:=lastcol, Criteria1:= _ Array("*" + sch + "*"), Operator:=xlFilterValues Else lo.Range.AutoFilter Field:=lastcol, Criteria1:= _ Array("*" + sch + "*"), Operator:=xlFilterValues End If Range("search_string").Select End Sub I will note that I have not recorded any Macros, but just typed both scripts in. When I try and filter nothing it's happening. My main goal is to be able to type a keyword and filter/populate the information related to it Please help.

Enstyle Planners on 2020-11-23 11:36:46
Thanks for sharing the information keep on posting this type of blogs!

Nick Ryder on 2020-11-20 15:53:29
Hi This is nearly perfect for my needs thank you, however I would like just the "replace" to be the result if possible please, I have tried in vain adjusting the formula without success. Thank you in advance

Trevor Beairsto on 2020-11-19 20:56:14
Hi Chandoo! I love the mind map! I would like to get a large laminate board made from it, but the printer lab said the resolution won't work to enlarge it to what I would love to have. Is it possible you might have a high resolution version? You are a great teacher by the way. Cheers from Canada.

CB on 2020-11-18 20:09:06
Hi i wonder if i can have a "Floating Table" in excel. similar to a floating picture. but i want to be able to edit that table as a regular table.

Robert Clark on 2020-11-18 09:54:57
Great stuff! I can understand how to add a slicer to the pivot table, but how do you implement the departmental selector on the 'Filter' formula scheme?

Fadli on 2020-11-17 17:47:03
I can download the file

Christophe Leribaux on 2020-11-17 10:21:20
Hi, to accomodate our project managers and the portfoliomanagers in the company (JSR, Belgium) I'm interested to test version of the portfoliomanagement files you present in the PDF. It the one with an overview of all projects and visibility of the details when you click on a project. (Page 5 of the PDF). I wonder if is possible to send me a BLOCKED version. If this would be acceptable for the users, I'd order the full package. best regards and thank you for you quick response, Christophe

Tom on 2020-11-16 14:09:00
The link does not work properly and I'm not sure how to actually get the graph to display like this, its frustrating me a tonne. I cant work out what to google either to find an answer elsewhere! :(

En on 2020-11-16 09:00:55
How does this work with the new 'Waterfall' charts in office 365 Excel? Arrow keys are not working. When using Ctrl+Arrow, the whole chart is moving. In the waterfall chart, it is very important to select data point to 'set as total'. Can anybody find and help. Thanks

Aarav on 2020-11-15 16:17:41
Very helpful and in depth article, thank you!

Erin on 2020-11-12 22:36:34
It really is the bestEST!!

Erin on 2020-11-12 22:04:50
This was a lifesaver! During the COVID pandemic one government agency handed us, another government agency, a half finished and complex data dashboard. To make things more complex, they were too busy to give us example reference data so we had to guess the variables. All of your tips are helpful. Thank you.

Gokul on 2020-11-11 08:28:49
How to generate sequential invoice number for multiple sheets. It will be a continuation number based on the input in different sheets. for example: Sheet1: 001,002 Sheet2: 003,004 whenever the sheet gets updated then the next number should appear in the both sheets. Please help.

USMAN on 2020-11-06 18:20:30
HELLO I want excel spread sheet for easy cricket betting

Chintan on 2020-11-05 19:03:38
Superb! thank you so much! Works flawlessly!

Rida Ali on 2020-11-04 17:41:25
Hi, Thanks for sharing the feature. Have successfully modified the code and used it in many excel files at work. Can we connect another workbook to this database and extract/modify/update? records. Regards Rida Ali

Venkatesh on 2020-11-03 17:30:05
Hi. This has really taken my interest.. I have huge data tables to work with...and I use vlookup to fetch certain data. I have different data in different sheets... Like customer sales (customer code, product code,qty, piece rate, total amount, branch code) data in one sheet Branch details in another (branch code, branch address, state , region) Customer Geographical Data in third sheet (region, region name) Product details in fourth sheet (product code, product description and related) Now I use a vlookup to get branch name, state and product name respectively into my main sheet. Now what I want is customer code, product code,qty, piece rate, total amount, branch code) data in one sheet, branch address, state , region, region name, product description Can't his be done thru data model... I tried but it's not working... Eitherway, I will gonthru thr session on e again and give a try... Any help, is appreciated. Thankyou

THANESHWAR SINHA on 2020-11-03 08:02:28
Formet Cell ME JAKAR Accounting+English (India) NHI AA RHA HAI KAISE LAYE. OR COMMA THUNDRED,THOUSAND,LACS,CORRE KAISE SET KARE

Keerthu on 2020-10-31 16:46:01
How to implement the same for all the sheets of entire Workbook using Excel Macros.

Daniel on 2020-10-31 08:27:42
Thanks for the insight about the excel charts! It is very important these days to visualize our data in the best way for other people to see. The flexible data manipulation using formulas or VBAs also allows you to make seriously interactive charts. In my opinion, learning those things can surely help you in visualizing excel data. Do you have some suggestions what formulas or VBAs command to learn for that?

ticket on 2020-10-30 14:12:33
Do you have any video of that? I'd like to find out some additional information.

Ibson on 2020-10-30 10:14:35
Able To Extract starts with the initial measurable data set and generates derived values ?.Users can insert and customize their numbers to professionally index business. Philip patchhere.com