Search results for query: *

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

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

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!

Hi I have these two formulae: =SUMIF(A:A,"abc",C:C) and =SUMIF(B:B,"xyz",C:C) can I combine them so that it sums the rows in column C that both "abc" appears in column A and "xyz" appears in column B? Thanks in advance

Is there a formula (or function) that will return the text before a comma within a single cell in Excel? For Example: Cells contains "Bloggs, Joe" and I want a formula or function that will return Bloggs. The "LEFT" forumla will not work as the comma might be in a different position in the...

Apologies but this is a repost of a thread I started in the wrong forum but I thought I might find the answer quicker in here by reposting the question: I have named a group of cells "Travel". They are currently empty and will be filled in by the users. I want the users to be able to add rows...

I have a spreadsheet that has US dates. They are in the format MM/DD/YY but when I open the file in UK Excel it thinks this is text and not a date. I don't seem to be able to convert these to dates or to UK dates. Has anyone got any ideas as to why this would be and a potential solution?

The following piece of code displays the SaveAs dialog box, exactly as desired when run but when the save button is hit, the dialog box disappears and the file has not been saved with the new file name. Any ides Sub SaveAs() Dim Filt As String Dim FileName As Variant Filt =...

Please could someone tell me what is wrong with this code: Sheets("Transfer").Range("InputRange").Copy ' From original workbook Workbooks.Open Filename:="****.xls" ' Opens destination workbook Range("CopyStart").Activate ' Selects cell in destinatiom workbook where data...

Hi I'm trying to move data from multiple worksheets to a new workbook using VBA. I believe I therefore have to do this from each worksheet individually. Here is the code I'm using at the moment which is incorrect: Workbooks("Old Book").Sheets("Sheet 1").Select Rows("1:1").Select...

Hi All Could you help me out again? I'm trying to loop a piece of code until cell A10 is reach, what is the loop until statement I need? Sub AddNewClient() Application.Calculation = xlCalculationManual Dim x As Long Dim r As Range Do Rows(ActiveCell.Row).Select...

Unfortunately the search function is down, so apologies if this has been posted before. I have a column that might have the text "Not Enough Data" in anyone of that column's cells. I would like a forumla that if the column has that text then return a statement such as "This deal has...

I am trying to locate all cells with a particular colour and clear the contents of these cells. The following code just continues to loop, despite that only a small number of cells actually has the colour in question, in a test spreadsheet (spreadsheet I'm trying to apply this to has thousands...