Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. HairyHippy

    After vlookup returns blank look left

    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...
  2. HairyHippy

    After vlookup returns blank look left

    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...
  3. HairyHippy

    After vlookup returns blank look left

    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...
  4. HairyHippy

    Multiple Value Pivot Table Filter

    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...
  5. HairyHippy

    Recorded macro not running

    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...
  6. HairyHippy

    Recorded macro not running

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

    Recorded macro not running

    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...
  8. HairyHippy

    Recorded macro not running

    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...
  9. HairyHippy

    Recorded macro not running

    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...
  10. HairyHippy

    Recorded macro not running

    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...
  11. HairyHippy

    Recorded macro not running

    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?
  12. HairyHippy

    Recorded macro not running

    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...
  13. HairyHippy

    Recorded macro not running

    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...
  14. HairyHippy

    Recorded macro not running

    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...
  15. HairyHippy

    Recorded macro not running

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

    Recorded macro not running

    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 ' '...
  17. HairyHippy

    Recorded macro not running

    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...
  18. HairyHippy

    #VALUE issue with linked files

    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.
  19. HairyHippy

    #VALUE issue with linked files

    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...
  20. HairyHippy

    Return everything to the right

    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!

Part and Inventory Search

Back
Top