# amend

1. ### Amend Formula to extract year from Cell

I have the following formula below =INDEX('C:\My Documents\[Comms2019.xlsm]Comms by month'!\$A\$11:\$N\$11,MATCH('C:\My Documents\[Comms2019.xlsm]Comms by month'!B1,'C:\My Documents\[Comms2019.xlsm]Comms by month'!\$A\$1:\$M\$1)) I would like to amend the formula to extract the year from cell P1 so...
2. ### if+index+match

hi all, any idea on what's wrong with my formula below, i cant return Khloe Kung result same as others user with same formula.? what should i amend and fix my issue? thanks all
3. ### if+and

hello all, i need hand on a IF+AND formula, how can i amend it with my request? =IFERROR(IF(AND(--LEFT(C59,5)>=--LEFT(B59,5),--RIGHT(C59,5)<=--RIGHT(B59,5)),"","WRONG"),"") is it possible to return one more request with "NO GIVEN" when it encountered "#N/A" thanks, have a great day
4. ### Chart series disappear in XL2016 when secondary axis changed

Help! I've got a large model with 30+ charts. They were created in XL2013 32bit, I've now got 2016 64bit. When I try and amend the bounds (upper or lower) for the secondary axes, all the chart elements (series, axes, legend) disappear and I get left with a blank chart. I tried deleting the...
5. ### Excel Formula Query - Adding OR to function

Hi aLL, Have the following formula which works fine, however want to add to it so that it looks for not just the text "A1" but also "B1", how can i amend the below formula? =IF(RIGHT(TRIM(D2),2)="A1","YES","NO") Thanks,
6. ### amend value when copied to show full numbers when pasted into new cells

Hi I have the code below where I copy data from Range AL2 to paste into B2 but the numbers look like 2.7Ex12 and I want this to show as 2700444444444 for example how can I amend that in the code? it is the first line below Private Sub CommandButton2_Click() Range("AL2"...
7. ### Returning 10 digits from text string?

I have a formula which has worked perfectly for some time, but I now have some variables in my source data and am struggling to amend it. Essentially I need to search a string of text and return a 10 digit number. Previously this always started with a “41” so I could use the formula...
8. ### Amend a data validation list code to accept lowercase letters?

Hi, Here is my code: With ActiveCell.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="D, E, F, G, H, J, K, L, M, N, P" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = ""...
9. ### Copy to lastrow

Nice simple code below - copies to the last non blank row. However I have numerous rows that appear blank, but have formulae in them which make them blank (IF statements with ="") Is there a way to amend the code so it ignores the "" cells? Thanks Sub Copy() lastrow = Cells(Rows.Count...
10. ### Restrict editing of some cells on a document

I have a spreadsheet I need customers to amend. I need them to be able to: Insert rows Not amend any cells contents in Column A Be able to edit the cell contents of Columns B-E I've tried playing around with protecting the sheet, and marking certain ranges as Locked, but I can't get it to...
11. ### Incorporating round function into formula

When incorpating the round function into my formula, it returns #value !. Without the round formula, I get no error =ROUND(IF((C2-B2)-1>0,TEXT((C2-B2)-1,"#,#0")&UNICHAR(9650),TEXT((C2-B2)-1,"#,#0")&UNICHAR(9660)),0)&"" It would be appreciated if someone could amend my Code
12. ### Conditional Formatting

I have the following conditional formatting below so that if the rate in Col A <>14% and the value <> 0 in Col E, then columns A:G that are part of the CF are highlighted in Blue =IF(AND(\$A2<>14%),\$E2<>0) I need to amend my formula so that if #Div/0! is in Col A and col E <> 0 then the...
13. ### VBA Amend Column Width (Problem is Merged Cells)

Hi All, I need to automatically amend a column width, which is easy with VB. Script supplied below. However, the author of the spreadsheet decided in their wisdom (d'oh!) to incorporate merged cells. Unfortunately, I need to retain these numerous and illogically placed merged cells. is there...
14. ### add text to formula

I have the following formula which gives me 0.52 . I want the result to appear for eg as 0.52:1, 0.59:1 etc =B79/B5 result in this instance 0.52 I tried to amend it as ="B79/B3 "&1&":1" and the end result is B79/B3 1:1 It would be appreciated if someone could kindly amend my formula
15. ### If(isnumber(search

How do I amend the code in IF(ISNUMBER(SEARCH"XXX" ...etc so if it ISNT "XXXX" please
16. ### amend vba code

Dear Guys Good Morning Can you amend my vba as per attached link https://www.dropbox.com/s/mqecoiuv3kwlnv2/TESTING3.xlsm?dl=0 Cell A6 JOHN MR SMITH AND MR STEVE JACK result should be Dear Messrs in B6 and Banking Facilities in cell C6. tHANKS/REGARDS RAKESH
17. ### Formua too remove dot

I have the following formula to extract the name and surname from an email address. I need to amend the formula so that dot (.) does not appear between the name & surname =PROPER(LEFT(B3,FIND("@",B3)-1)) your assistance in resolving this is most appreciated <b></b><table...
18. ### Amend Message Box to include Text

I have the following code below Range("f1").Select Dim myValue As String ActiveCell.FormulaR1C1 = InputBox("Enter the Report month and year") Range("F1").NumberFormat = "MMM-YYYY" I would like to automatically include text in the message box Reporting period : so that when the month...
19. ### Amend Offset to pick up row number

I have a match function in Col F eg F5 to find the row number I would like to amend the offset function to automatically include the row number =OFFSET(Sales_Rep!E1357,0,COLUMNS(F1357:AQ1357)) I tried to amend as follows but cannot get it to work...
20. ### Sum(Sumifs -- not returning as expected

Can't understand where this formula is going wrong. =SUM(SUMIFS(A:A,B:B,{"bob","tim"},C:C,{"North","West"})) If I amend all entries in col C to west it returns for tim and if I amend all to North it returns for bob. I need it to return for both bob and tim irrespective if north or west.

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back