Recent content by HairyHippy

It would be great if someone can suggest the formula to use but in the meantime I've found a work around. Sheet 2 is actually a pivot table and therefore for the outter months, I can just make equal to the last month available, therefore simplfying the lookup formula and then when the next...

Hi Skip Can't upload a file from work unfortunately with the example data (or a mock up of it). I'm using a vlookup formula, happy to switch to an index/match combo. Basically the if statement would be something like: =IF(vlookup(A2,sheet2!A:M,2,false)="",look left in same row on sheet 2 for...

Is there a forumla that after doing a vlookup and that result is an empty cell that it looks back in the same row as the empty cell until it finds a cell with data? For example Sheet 1 has a list of currencies in column A and the other columns balances by month until say the end of this year...

Hi I currently have the following code: Sheets("New New Deals").PivotTables("PivotTable2").PivotFields("EMEA Mkts").PivotFilters.Add _ Type:=xlCaptionEquals, Value1:="BENELUX" But I need to select 2 values but the following won't work: Sheets("New New...

Ok getting odder.... New file, all else closed. Sheet 1: Region Bal ABC 100 DEF 50 GHI 8222 ABC 3654 DEF 546984 GHI 8548 Create Pivot in new worksheet - becomes Sheet 4. Put Region in Row Labels and Bal in Values (Sum of Bal). Change field setting of Region to Sales Team. Pivot Table...

Another thought as to why it might or might not be working is checking or unchecking "Classic PivotTable layout"....

Skip thanks for your help with this, much appreciated. I can't work out why it works for you and not for me so I'm going to stick to my orignal code for now, which was the "with" statement one above. What is odd is in that one it uses Sales Team and works perfectly fine but won't for this...

Skip thanks but the issue is due to using custom names rather than original source names, I've proved this out both in my orginal and recreating along the same lines as you did. You can test this if you like in the one you created. Go to field settings and change the name from something other...

Ok that's precisely this issue. Basically the base data used to create the pivot has a column that called Region (this can't be changed as it's in the source system. Pivot then created and the heading in the pivot changed to Sales Team using the field settings option, so source still says...

But neither sales team or region works as stated above. Are we saying that you can't change the name using the field settings options or a macro won't work? I'll change it back to region in the field settings form box and test... But the thing is that in the report I need it to say Sales Team...

Heading 1 Heading 2 Heading 3 Heading 4 Sales Team Deal Stage Rep Name Total ($M) Region is the orginal source name, Sales Team is custom name. I wonder if this is what it is not liking?

I'm afraid I can't the data is confidential. I wish I could, it obviously make this a lot easier. What I did find interesting is that the macro I recorded has "Region" in it, yet the last set of code I posted above has "Sales Team". I changed the Region to Sales Team to see if this would...

Wooh hang on a minute Skip, don't forget this was a recorded macro and I thought, but maybe I'm wrong, would put in what it found in the sheet e.g. all the naming conventions. I didn't touch what excel had recorded. I've supplied all I can regarding the issue. I will also try to recreate in...

Nope, 'fraid that didn't work either. Essentially what I'm trying to do is quicken up the macro by using a label filter and selecting that filter by code rather than the following code, which only loops and thereofre takes forever to run: With...

I posted the code in the above post. It still doesn't work, get same run time error.