1. I

    Quick Help

    Hi friends, Can someone help me with working out a formula for the below, my head is hurting from getting wrong a few times. Column A is a Yes or No Column B is a number 1 to 4 Column C needs to be a formula that returns the following "Code 1" if column b is 2 and column a is yes "Code 2"...
  2. H

    Excel struggles to respond with certain formula

    I enter the number 45 into cell A1. A2 formula: A1+1 (Returns "46") A3 formula: A2+1 (Returns "47") A4 formula: A3+1 (Returns "48") I have this sequence for about 200 rows. I understand that you are able to just drag down to continue the sequence of consecutive numbers although this is in...
  3. M

    Size limit on Modules in VBA?

    I'm nearing the completion of a macro-enabled workbook that has many modules. I remember seeing some forum posts online that talked about there being a size limit to modules. It seems that the unofficial word is that the limit is 64K for each one. One post I saw suggested to type the...
  4. H

    Pivot Table returning Zero instead of Blanks

    Hi I have a pivot table which is taking data from a field which is populated as follows =IF(VLOOKUP(B90,'Order Download'!A:BP,62,0)="","",VLOOKUP(B90,'Order Download'!A:BP,62,0)) If this lookup returns a value, all good and i get that into the pivot. If it returns blank (which is the same...
  5. W

    Converting a Hexadecimal with a length of 14 to Hexadecimal

    Dear All, I am trying to convert a hexadecimal string eg <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>EAAA863573781B which should return <style type="text/css"><!--td {border: 1px solid #ccc;}br...
  6. K

    Macro to mulitply a cell by 1,000

    Need a macro that multiples the value in a cell by 1,000. Sometimes the cell value is a positive amount, other times it’s a negative amount. If a cell has a value of 8, I recorded a macro that does F2, *1000, then home, then +. The macro works fine and returns 8,000. But when the cell has a...
  7. K

    get all sheet names starting with a specific letter

    I have this vba code but it returns ALL of the sheet names. But I want another-one that returns the sheets that start with the letter "D" and list them in column H58 on "switchboard". Sub WORKSHEETLIST() Dim ws As Worksheet Dim Counter As Integer Counter = 0 For Each ws In...
  8. C

    Formula identifies number - returns #value in the end - why?

    Hi, I have a long formula and have taken snapshots of the last steps in the evaluation chain: The formula identifies the value I am looking for but in the end it returns #value error. Can I please have some guidance on how to solve this issue? :(
  9. R

    Sumproduct Question

    I have been working on this formula for a couple of days. I don't think countifs can do it and after searching this forum I found this: =SUMPRODUCT(I:N<>0)*(E:E=Q3)*(F:F=55). The prodblem is that it returns 0 when the correct answer should 5. What am I doing wrong? Thanks for the help. Rufus
  10. willow1985

    IF function for returning month name with data that has zeros

    Hello, I have a column that has a list of "0" and dates. I am looking for a formula that will return the Month name on any cell that does not contain the text "0" and if it has a "0" return a blank cell. Here is the formula I wrote but unfortunately it does not seem to be working...
  11. E

    VBA: Issue with capturing cell formatting using Range.DisplayFormat.NumberFormat

    I'm experiencing an issue where the manual check of formatting for a particular cell does not match what the Range.DisplayFormat.NumberFormat returns (I've tried .NumberFormatLocal also). It appears it is replacing parenthesis characters () with a hyphen -. What I'm trying to do: A client has...
  12. W

    Value returns null and error from text to numbers in query

    Hello I have imported a text file to excel using power query. I want to convert a columns value from text to numbers but when i do so it returns "null" and "error" values throughout the column. I am using excel 2016. Gratefull for any help i can get :)
  13. T

    sumif for non-blank, non-contiguous cells

    I am trying to write a formula to total data in column C and column E (C+E) only when C or E have data. In other words, when C and E are blank, i would like the formula to return "-". When C or E have data, including a 0, i would like the formula to add C+E. My only experience with sumif is...
  14. J

    Extract multiple matches into separate rows

    Hello world. I'm having an issue where I have and index and match with multiple return values but running into problems. Here's my setup: A B C D E 1 2 10A 3 [first] 4 [2nd] 5 [3rd] 6 [4th] =IFERROR(INDEX('BETA Care Plans'!$B$1:$B$215,SMALL(IF('BETA Care...
  15. G

    Google sheets vlookup

    I have a vlookup in Google Sheets that isn't making sense to me. =MID(A3,search("(",A3,1)+1,6) returns 263972 =vlookup(263972,$S$3:$X$158,3,0) returns the expected result =vlookup(MID(A3,search("(",A3,1)+1,6),$S$3:$X$158,3,0) returns N/A. (Did not find value '263972' in VLOOKUP evaluation.)...
  16. P

    sum = 0 in error

    I have a column of numbers that sums erroneously to 0. If I add each cell A1+A2+A3, etc it returns the correct value but the sum function always returns 0. I've ensured that all the field formats are numerical. Any ideas what's causing this problem?
  17. L

    CountA issues

    Using COUNTA to exclude blanks – having issues I have a spreadsheet that has 101 rows of data (First row contains column headings) Column B is a text field. There are four blank cells, so the correct count is 96. =COUNTA(B2:B101) returns 96 =COUNTA(B:B) returns 98 =COUNTA(RGIndex!Authors)...
  18. M

    INDEX-MATCH with LEFT 10 digits

    I am currently trying to index match with only the first 10 digits of the lookup value. =index(R:R, MATCH(E2,AQ:AQ,0)) this returns my match if they are exact, the problem is I am working wit data that is not to be modified. what I need (AQ:AQ) , what I need with extra string(E2), value from...
  19. K

    Date if

    How do i create an if statement that looks at cell C6 and says if it is between 1/1 and whatever year and 3/31 whatever year that it returns 1st qtr or if it is between 4/1 and 6/30 then 2nd qtr and so forth?
  20. M

    extract data between values based on seperate input

    I have some data in A1 that looks like the following: PA1*COL 1*50*Snack 1 PA2*3*100*1*100 PA1*COL 2*100*Snack 2 PA2*52*100*0*0 PA1*COL 3*100*Snack 3 PA2*2*200*1*100 PA1*COL 4*50*Snack 4 PA2*7*50*0*0 PA1*COL 5*200*Snack 5 PA2*100*0*0*0 In A2 i need to be able to type a value, in this example i...

Some videos you may like

This Week's Hot Topics