Search results

Crossposted at http://forum.chandoo.org/threads/formulas-using-vba.20736/ Anand: Can you please post just one question in future? Posting the same question in two separate places just adds extra work. Have you considered whether a PivotTable can assist you with this problem?

Cross posted at http://forum.chandoo.org/threads/formulas-using-vba.20736/

Anand: Using VBA to populate the worksheet with an excel function will not magically make Excel calculate any faster. Excel is still generating the results, not VBA. Furthermore, it is not clear to me from your sample file exactly what you are wanting to do. Can you elaborate further?

Do you know that EXcel can automatically create separate copies of pivots - one for each item in a PivotFilter - and put them in separate tabs? It's called 'Show Report Filter Pages' http://www.pivot-table.com/2012/10/29/create-worksheet-for-each-pivot-item/

Re the report filters not being 'additive' like they are with tables, one way around this is to use Slicers, which grey out any items that aren't in the currently filtered subset. On the upside, they are easier to select things from. On the down side, they take up a bit more screen real-estate...

Are you trying to filter the PivotTable for each different client, Dave?

Hi David. Slicers can act on more than one PivotTable if they are based on the same data source. See my article at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

Hi Brian. Got your file, thanks. @NARAYANK991 is right: You can greatly condense the information down into a small table. For instance, just by giving the lower bounds of each pricing band, table3 can be reduced from 2 million cells to this: If you create similar tables for all your data...

While a direct calculation would be good if possible, this volume of data won't pose any problems whatsoever, because we're only doing two lookups on it: one for the column, one for the row. This can be done lightning fast - particulary because we can probably use the 'Exact Match' form of...

Sorry, I don't understand your requirements and so am unable to help

They aren't used much...primarily because few people realize how powerful they are when it comes to syncing pivots. They should be used much, much more. Embrace them, my friend. They are the future. I wrote something on them at...

Brian: See attached. I've converted any data to Excel Tables whereever I could. This is a habit you should get into. Also, I've unmerged your cells. Don't use merged cells if you can avoid it...they make things difficult in the long run. How many rows are your lookup tables? Hundreds...

@shrivallabha: in terms of Arg1, I see at http://excelxor.com/2014/10/20/extracting-numbers-from-a-string-1-consecutive-numbers-at-start/ that XORLX has perhaps a better approach:

Ah, I think I understand your requirements now. See attached, that uses 2 PivotTables and some slicers to connect them. You can actually just use the slicers, and get rid of the first PivotTable if you want. You can also just put some slicers on the original table, too...and not have...

So in Sheet4 where you say "Instead of showing up with numbers I want to show up the data instead here", what data are you talking about? Your file isn't clear to me.

http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

Why not just use a Pivot?

Before you make any changes to the PivotTable, do this: ActiveSheet.PivotTables("PivotTable1").ManualUpdate = TRUE ...and then after you've made all the changes, do this: ActiveSheet.PivotTables("PivotTable1").ManualUpdate = FALSE That will stop the PivotTable recalculating every time you...

Hi ryanchild292. Addin is not available yet. When it is, I'll be sure to post over at the Chandoo forum and here.

@shrivallabha Have you ever used this in practice?

@shrivallabha Might be safer to replace 99 with a larger number, or even 9^9 (which will be way larger than the largest list that this would ever work on, to be sure). @mariur89 Shrivallabha's formula is better than mine, so use that one. (And I'll put it in the book).

Hi Shrivallabha. I never thought of using LOOKUP. Great stuff. Seems the difference in things returned is purely down to sort order in the lookup list. For instance, given the lookup terms Tax, Taxi, and Taxidermist, then my formula requires the lookup list to be sorted Z to A whereas yours...