Search results

SUMPRODUCT may be? Like this: =SUMPRODUCT($B$13:$J$18,((B$10:J$10=$A$3)*(B$11:J$11=$A$4))*($A$13:$A$18=A8)) See advanced sumproduct queries page for explanation of this technique. https://chandoo.org/wp/advanced-sumproduct-queries/

Also some of the new fanged charts like sunburst, map charts don't work with linked cells or only support it for certain parts of the chart.

Hmm, not sure what sorcery Bosco's formula is doing But here is another one that seems to work with your data. Slab: =INDEX(E$2:E$60, MATCH($M3,IF(($B$2:$B$60=$J3)*($C$2:$C$60=$K3)*($D$2:$D$60=$L3)=1,$E$2:$E$60,NA()))) Scheme: =INDEX(F$2:F$60...

You can't fill like that. Either use Area Charts (with date axis) so you can get the precision or just use Spreadsheet grid with conditional formatting or a bunch of columns with gap=0 or something else.

You are right. It is not clear what OPs code is doing, so I assumed everything is happening in one file, in which case ThisWorkbook = ActiveWorkbook

Unless you are running the code from Add-in, ThisWorkbook refers to Active workbook, so no need to activate it again. Refer to MS help on this - https://docs.microsoft.com/en-us/office/vba/api/excel.workbook

@Hany ali & @ravikiran Why not use Power Query to do the merge. You won't be able to get it in the exact format (with merged cells or blank cells), but that is just cosmetic. If you have Excel 2013 or above, you can use it. Follow below steps. Open new file, go to Data > New Connection >...

Interesting... without messing with actual DAX, here is a simple way to do it. Make two charts, size them same way and overlay one on top of another. Now link the slicer to interact with only the top chart. That should do the trick. If you want to venture in to DAX, you can use ALL() to clear...

@GraH - Guido Show us your creativity and make a few...

Hi Rhon... If you sort a pivot table, whenever you refresh (or change filters) the sort is reapplied automatically. This is the default behavior. Have you tried setting up the sort and refreshing data on your pivots?

Hey @vijay.yvcs We offer certificates on all our courses. That said these are not accredited by Microsoft or anyone. To be honest, it is very hard to assess student ability in technically complex online classes. For our training programs, see https://chandoo.org/wp/training-programs

@Grant Bradley Interesting question. I had to wikipedia namograms. They might be easy to construct (not the weird circular ones) assuming we know the equations. I couldn't figure out equation for the MFI of combined resin from what you wrote. That said, I think you can build a normal...

Also read Bill Jelen's free ebook in case you want to learn more about these newly added functions. They will forever change the way we write complex array formulas and set up calculations. https://www.mrexcel.com/download-center/books/2018/ExcelDynamicArraysStraightToThePoint.pdf

@bharath You can use a formula based CF rule to highlight such items. To prevent duplicate rules from being entered, you can apply same logic in custom data validation too. For CF: Add a rule with this =COUNTIFS($C$2:$C$21,$C2,$D$2:$D$21,$D2)+COUNTIFS($C$2:$C$21,$D2,$D$2:$D$21,$C2)>1 and...

@Travis Frank Interesting question. You can use Conditional Formatting > Between rule with formulas to check this. See attached file. Here is how it works. We want to highlight 10 cells that are between smallest positive stock & 9 prior weeks. =SMALL(IF($C$5:$C$69>$C$3,$C$5:$C$69),10)...

@Lolo... another option is if you are using measures in the pivot table, then you can create the measure such a way that it would return BLANK() if the count / sum etc is 0 (ie there is no value for the city). Then when you put this measure in pivot (and nothing else other than city), pivot...

Having seen a preview of this (thanks Hui for sending it), couldn't wait for the reveal :) :awesome:

Interesting problem. I gave up formula driven approach half-way thru once I realized how long and confusing my formula got. So off to PQ. There were some excellent solutions here. I wanted to create a function to generate pairs. To make it generic, I made two functions. one for creating...

If you are using Excel 2016 or Office 365, why not use Power Query to do this for you. You can easily setup a query to read part numbers, ignore quantities from another list and create new output (either comma delimited as you have or some other format). Here is an example of how to do this. Of...

Oh yes, simply use either Power Query or consolidated pivot tables. If you can post a sample file I can show you how to do this with PQ.

@tiong999 Not sure what exactly your end goal is, but it seems you want to kind of transpose your data. If so, it is better to use INDEX. See this: =INDEX(B$4:CF$5,1,B$12+ROWS($A13:$A$13)) in B13 and drag down, sideways to get some numbers. Read more about INDEX here...

@Cornelius deWeever Welcome to our forum and thanks for posting a question. Without looking at your file, it will be very hard to understand exactly what mistake you are making. That said, try this: See if your formula cells are marked as "TEXT" from Home > Format area. If so, switch to...

@DWpzaz Welcome to Chandoo.org forums and thanks for your post. You can use Power Query to turn your table to a text pivot easily. Use below steps. Load your data in Excel. Convert it to a table (Ctrl+t) Load this to Power Query Select student ID column and group by. In the operation area...

@Aquila... For excel file, you can also add this "Discount" column if doesn't exit using either IF then or try otherwise constructs like below. =if Table.HasColumns(#"output table", "Discount") then #"output Headers" else Table.AddColumn(#"output Headers", "Discount", each 0), = try...

@snjpverma I am not sure if I know what you want. But assuming you just want to know for each month the shortage / excess values, you can use below approach. You need a column to identify tax dates. You have colored them in yellow, but formulas can't read cell color. So let's say column E...