Search results

Anyone else have this issue? I upgraded to Excel 2016, but when I go to upload a file, no xls or xls* files of any type show up in the Open Files dialog when I go to attach a sample file here. For instance, here's the view I get from using File Explorer to view my folder, that confirms that I...

What should happen in the event of other combinaitons? e.g. If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is "0"? By far the easiest way to do this is via a lookup table, where you have a lookup table that concatenates (joins) Condition 1 & 2 & 3 & 4 together into...

Any reason you can't just use a PivotTable? Or slicers? Or both? What version of Excel do you have?

Hmm. For some reason I can't seem to upload the sample file. That's happened a few times recently. I'll try again later.

It might be an extra space, or a non printing character, or similar. I blogged about a similar situation at http://dailydoseofexcel.com/archives/2014/08/09/one-of-these-things-is-not-like-the-other/ Furthermore, there's a good explanation of non printing characters at...

Here's a good forum post discussing this kind of thing further: http://forum.chandoo.org/threads/search-for-multiple-texts-in-cell-return-specific-text.20507/#post-123391 You can also use this formula in my example above: =LOOKUP(8^5,SEARCH(Table[Terms], [@Input]),Table[Amount]) I learned that...

Another alternative is to use a lookup table with wildcards, and carefully order your list of lookup terms in order of preference. Here's a screenshot from a book I'm writing that has such an example:

Moiz: the image you have attached is for a Pakistan Identity Card.

Sounds to me like you need to do a regression analysis on this. I've previously done two-way interpolation, as a formula challenge. Very complicated. See http://forum.chandoo.org/threads/formula-challenge-010-two-way-interpolation.11305/

I've added a comprehensive answer to that StackOverflow link that draws on content from my book.

Is there any reason you can't use a PivotTable to do this? That would fix the issue completely.

Yes, you really should run a parameterized query, so that SQL is doing all the heavy number crunching. These links may help. http://datapigtechnologies.com/blog/index.php/passing-multiple-values-to-one-parameter/...

I'd add "Best used when you want to perform advanced sums with multiple conditions and you can't use a PivotTable or the more-efficient SUMIFS"

Using a UDF to do a simple INDEX/MATCH is potentially a very bad idea. These UDFS are much slower than the formula equivalent, and will recalculate any time you delete any cells in the file. So if you have thousands of them or more, you could be in for a very long wait.

Here 'tis.

Weird: I can't seem to see the file in the directory when I'm trying to upload it here. I know it's there, but the window that comes up when I click Upload a File doesn't show it in the folder. Same thing happenned yesterday. I'll try a restart, see if that fixes the issue

@All - I picked up an oversight in the code relating to non-OLAP pivottables, that resulted in any slicers that referenced the field being filtered getting deleted. Have amended that in the attached code.Bound to be more things that I fix or improve over the next few weeks too. @Asheesh...

Glad you like it. I have a new friend at Microsoft who's using this too. I told him to pass it on to the Excel Dev team there, with the hope that they say: Cool. Now why don't we do that?

Sample file attached, that lets you invert a filter in a Table or PivotTable, plus filter them based on a whole bunch of crazy wildcard combinations. Very complex code, using English version of Excel 2013. Should run on Excel 2010 or later. Very beta. Save your work first. Love to hear of any...

You might also like the approach I take in my posts on the Chandoo blog: http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT at looking up an item in a list. SUMPRODUCT is used for crunching lots of numbers together based on conditions. Yes, it conceivably be used in place of VLOOKUP or INDEX/MATCH. But it should not be.

@bobhc Tables don't let you invert a filter at the click of a button. @dramnath1981 - I have some code that does this. I'm in the process of making some changes to it, but should have it finished in a couple of days. Email me on weir.jeff@gmail.com if you like and I'll forward it to you for...

I'm in the process of working up some cool code that will let you filter Tables and PivotTables on as many criteria as you like, including <> and wildcards. It's pretty neat. Flick me a line at weir.jeff@gmail.com if you want to try the beta version. (I'm probably about 48 hours away from...

True. But it's still pretty messy, compared to 2013. Here's an example, with "Hide items with no data" checked: ...and here it is with the 'Visually indicate items with no data' checked: One does pretty much what Kiki wants. The other gives an indication for sure, but it's nowhere near as...

Sorry, badly worded. While Excel 2010 has Slicers, I understand they don't have that option "Hide items with no data". So in Excel 2013, Slicers let you accomplish something similar to what Kiki wants. But in Excel 2010, they don't...because they still show all items. (Although they may...