Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If the range of the cells are blank then: (excel 2000)

    I have a formula here that Legare helped me develop.
    the formula bascially looks in cell J23 and if its blank then the formula is ran if not then the formula returns a blank.

    I would like to modifiy it so that instead of looking a just J23 to see if it is blank. look at J22,J23,J24. If these are all blank rin the formula. I know I can use the and function but I was wondering if there is to define the range J22:J24="",formula?
    thanks

    =IF(J23 <>",",IF(ISERROR(INDEX($A$8:$A$17,MATCH(B25,$B$8:$ B$17,0))),"",INDEX($A$8:$A$17,MATCH( B25,$B$8:$B$17,0))))

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If the range of the cells are blank then: (exc

    replace IF(J23 <>" with If(counta(J22:J24))=0, the rest of your formula
    or
    if you want to use the defined range, you can create a range called "BLANK" with "Refers to:" of =COUNTA(Sheet1!$J$22:$J$24)=0
    Your formula would then become.
    =IF(BLANK,"", the rest of your formula

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If the range of the cells are blank then: (exc

    unforuntely, my problem is not that easy.
    I will try to explain.
    In cell J25 the formula strFormula will be added.
    J24:J22 have different formulas which are calculated if conditions are met. if no condition is meet above, then the formula in J25 returns a value.
    I have a macro which builds the formula though catenation depending on the varible conditions a user picks in a form.
    the user can build as many formulas, but 1 will be a standard needing no conditions to return a value. This standard on has to look at the cells directly above
    j22:J24 to see if the is a value in them if not then use the standard formula.

    hope this makes sense

    The range J22:J24
    strFormula = "=IF(Counta(J" & lngRowNum - 1 & ":J" & lngRowNum - 2 & "=0),"",IF(ISERROR(INDEX($A$8:$A$17,MATCH(B" & lngRowNum & ",$B$8:$B$17,0))),"""",INDEX($A$8:$A$17,MATCH( B" & lngRowNum & ",$B$8:$B$17,0))))"

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If the range of the cells are blank then: (exc

    Change the formula strFormula =

    "=IF(Counta(J" & lngRowNum - 1 & ":J" & lngRowNum - 2 & "=0),"",IF(ISERROR(INDEX($A$8:$A$17,MATCH(B" & lngRowNum & ",$B$8:$B$17,0))),"""",INDEX($A$8:$A$17,MATCH( B" & lngRowNum & ",$B$8:$B$17,0))))"

    to:

    "=IF(N(J22:J24)<>0,"""",IF(ISERROR(INDEX($A$8:$A$1 7,MATCH(B" & lngRowNum & ",$B$8:$B$17,0))),"""",INDEX($A$8:$A$17,MATCH( B" & lngRowNum & ",$B$8:$B$17,0))))"

    the quotes need to be doubled up <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

    The J1 to J5 is to just make the range work. You'll have to use ref edit in your user form to specify the range of manually change it yourself

    rather then the Counta i would use the N function!!

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If the range of the cells are blank then: (exc

    Hi Matix thanks for the help

    the N funtion doesnt seem to work??

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If the range of the cells are blank then: (exc

    Did i say N, sorry, I mean T.

    replace the N with a T and the
    so
    T(J23:J25)<>"","",and your formula

    dont forget those double quotes <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •