Search:

Type: Posts; User: steve the fish; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.21 seconds.

  1. Re: VLOOKUP not finding data that is present

    Your formula is wrong but if its showing na error then its not finding the lookup value in column D of the sheet. Use CTRL-F to confirm the value is there. Make sure entire cell contents is checked.
  2. Replies
    5
    Views
    69

    Re: IF Statement - brain fog!

    There is a principle of mathematics that multiplication and division are done before addition and subtraction so maybe put some brackets around this part:

    C4-D4*G4

    to become

    (C4-D4)*G4
    ...
  3. Replies
    6
    Views
    92

    Re: Array Formula Issue

    Its because AND doesnt produce an array. If you need AND use multiplication.
  4. Re: converting a 6 digit string into a 3 character string(HELP)

    Hi. Test this:

    =CHAR(MID(A1,LEN(A1)-4,1)+IF(MID(A1,LEN(A1)-5,1)="3",48,64))&CHAR(MID(A1,LEN(A1)-2,1)+IF(MID(A1,LEN(A1)-3,1)="3",48,64))&CHAR(MID(A1,LEN(A1),1)+IF(MID(A1,LEN(A1)-1,1)="3",48,64))
  5. Replies
    4
    Views
    57

    Re: SUMIF - dates, ignore times?

    You can using SUMIFS. Greater than or equal to the date and less than the date +1.
  6. Replies
    5
    Views
    91

    Re: Lookup and Populate cell

    Put this in A3 and copy down 31 cells. You may need to change the year if it happens you need a leap year February. Is that what you mean?

    =IFERROR(DAY(ROWS($A$1:A1)&LEFT($A$2,3)&2019),"")

    May...
  7. Re: VBA using a set range inside an indirect formula

    You could do this. YearRng then becomes a string which you can concatenate into the formula string:


    YearRng = Application.InputBox("Please select the year range for the first location in your...
  8. Re: slow formula. Is there a faster way?

    You still didnt really answer what i was thinking as if you delete row 84 your formula then has a different lookup value. It may be best to give a small sample of your sheet so we could take a look.
  9. Re: slow formula. Is there a faster way?

    Cant say as i really understand that. If you delete row 84 then your vlookup formula, for example, has a different lookup value. Hows does that all work? Id suggest avoiding the deleting. Why is that...
  10. Re: slow formula. Is there a faster way?

    The 2nd one can be simplified to:

    =SUM($G$84:G84)

    assuming that your lookup value is within the lookup array. If it isnt use what i gave before.
  11. Re: slow formula. Is there a faster way?

    Do these work the same way?

    =VLOOKUP(A84,CAPACITY!$AM:$BL,26,FALSE)
    =SUM(INDEX($G:$G:G:G,MATCH($A84,$A:$A,0),0))
  12. Re: IF(ISNUMBER(SEARCH formula considering 2.5 and 5 as same. How to check for exact match?

    You wouldnt normally do a lookup type of formula as you have tried there. Id be creating a lookup table. This is how it would look hardcoded into the formula:
    ...
  13. Replies
    3
    Views
    118

    Re: INDEX-MATCH with LEFT 10 digits

    Put =LEFT(E2,10) in a cell. Copy it. Press CTRL-F. Paste it into the find what box. Click optionsand make sure match entire cell contents is checked. Highlight column AQ. Press find. Does if find...
  14. Re: How to leave blank if no data entered

    Hi. Just use an IF test at the start:

    =IF(C2="","",IF(DATEDIF(C2,TODAY(),"y")=0,"",DATEDIF(C2,TODAY(),"y")&" years, ")&IF(DATEDIF(C2,TODAY(),"ym")=0,"",DATEDIF(C2,TODAY(),"ym")&" months,...
  15. Re: How to change vb script to use active row

    This would do that:


    Dim str_find As String, c As Range

    str_find = "whatever"
    Set c = Columns("A").Find(What:=str_find, LookAt:=xlWhole)
    If Not c Is Nothing Then c.Offset(0, 3).Copy
  16. Replies
    8
    Views
    112

    Re: Alingment of text in one cell

    Your options ive outlined above. If it moves to a new line its because there isnt enough space even if you think there is.
  17. Replies
    16
    Views
    168

    Re: Arguemnt not optional message

    Thats because it needs worksheet change as i said. With selection change nothing happens until you change the selection. With worksheet change its when you change the cell contents.
  18. Replies
    16
    Views
    168

    Re: Arguemnt not optional message

    Maybe try this in the worksheet module:


    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, c As Range

    Set rng = Intersect(Target, Rows(6))

    If Not rng Is Nothing Then
  19. Replies
    16
    Views
    168

    Re: Arguemnt not optional message

    If there was something in the row you can convert it to uppercase but there isnt an uppercase format as such.
  20. Replies
    8
    Views
    112

    Re: Alingment of text in one cell

    Those are not 'big spaces'. A space is a space. They are spaces that you have asked to justify across the cell width. If your 'big space' became small then you just end up with left alignment.
  21. Replies
    16
    Views
    168

    Re: Arguemnt not optional message

    What is it you are trying to do? There wont be anything in those cells.
  22. Replies
    8
    Views
    112

    Re: Alingment of text in one cell

    Hi. Im afraid those are your choices. Left align, right align, centre or justify. If you dont like the look of it your other options are to resize the cell or alter the text size until you do like it...
  23. Re: How to get the address of the formula's result from 2D array (Matrix)

    The only way to do that i think is to search for the result in the matrix. That isnt as bulletproof as using the formula conditions to build the address formula.
  24. Replies
    12
    Views
    542

    Re: help with a simple VBA code for excel form

    Try using:


    CDate(TextBox1.Text)

    rather than just Textbox1.Text
  25. Re: How to get the address of the formula's result from 2D array (Matrix)

    You could do this:

    =ADDRESS(MATCH(TRUE,INDEX($A$2:$A$4="H. Criteria 3",0),0)+ROW($A$2)-1,MATCH(TRUE,INDEX($B$1:$D$1="V. Criteria 2",0),0)+COLUMN($B$1)-1,4)
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4