Results 1 to 6 of 6

20051031, 19:44 #1
 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))))

20051031, 19:59 #2
 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

20051031, 20:11 #3
 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))))"

20051031, 23:20 #4
 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!!

20051031, 23:35 #5
 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??

20051031, 23:41 #6
 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>