Search results for query: *

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.

1. Yes it is. 2. Yes it is. By adding the sheet("name") in front makes no difference and I also tried changing the pivot name to see if that would resolve. I also re-recorded and started on a different sheet to ensure correct sheet selected. Sub Macro6() ' ' Macro6 Macro ' '...

I recorded the following macro: Sub Macro5() ' ' Macro5 Macro ' ' ActiveSheet.PivotTables("PivotTable2").PivotFields("Region").PivotFilters.Add _ Type:=xlCaptionBeginsWith, Value1:="ceema" End Sub Yet when it runs it returns a run time error 1004: "Unable to get the pivotfields...

Thanks macropod! Just going to have to remember when I'm updating file1.xls every month to have file2.xls open and hit F9! Not too much of a pain.

Hi I have a .xls (file1.xls) that is lined to another .xls (file2.xls). The formula used in the vast majority of cases in file1.xls is: =SUMIF('[file2.xls]Sheet1'!$A:$A,$A$2,OFFSET('[file1.xls]Sheet1''!$C:$C,0,$B$2)) $A$2 in the above forumla is another formula...

If I have a cell that has text in it, say a name like "Joe Bloggs", how do I tell Excel to find the space and then return everything to the right of the space? In this case the answer would be Bloggs but in "Charlie Brown", the answer would be Brown. Thanks in advance!