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

1. 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. 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
=IF(BLANK,"", the rest of your formula

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