Can ISTEXT search a range

**mike4545** · 07-05-2007, 05:18 PM

I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?

I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.

Thanks for your help.

Mike

**oldchippy** · 07-05-2007, 05:34 PM

Try this

=SUMPRODUCT(--(ISTEXT(E135:E144))*(--(G135:G144)))

**mike4545** · 07-05-2007, 08:31 PM

Hi

Thanks for your reply. In working with my new sheet I think I have to change my request, just a little. Is it possible to change the g144 in the formula:
=SUMPRODUCT(--(ISTEXT(E135:E144))*(--(G135:G144))) to any text/number such as "AF29" which is located in the column to the right of each cell E135 to E144?

Mike

**oldchippy** · 07-06-2007, 04:15 AM

Try this,

=SUMPRODUCT(--(ISTEXT(E135:E144))*(--(G135:G144<>"")))

or

=SUMPRODUCT(--(ISTEXT(E135:E144))+(--(G135:G144<>"")))

**mike4545** · 07-06-2007, 01:07 PM

Hi

Thanks for your reply.

I tried both formula's (AND TRIED TO ADJUST IT BUT NO LUCK) and they both work but not with the correct answer. I'm sure if it's a cell format issue or what. the correct anaswer is "Q21". I've copied the sample cells as follows:

Q21 Q21
##### V26
##### AA24
##### AF29
##### AK27
##### AP25
##### AU28
##### AZ23
##### BE30
##### BJ22

I've had to make some cell location changes so the new cell location of "Q21" above is F107. Q21 is arrived at with the formula: =IF(ISTEXT(Q21),G107)
Cell G107 has Q21 located in it.

The answer I get with your 1st suggested formula is 1 and 11 with the second. I'm sure what the issue is! If you want me to send a sample spreadsheet please let me know. Thanks again.

Mike

**oldchippy** · 07-06-2007, 04:11 PM

Hi Mike,

If you would like to post your sample spreadsheet I may be easier for me to understand better.

Thanks

**mike4545** · 07-06-2007, 05:40 PM

Here is the sample sheet.

mike

**oldchippy** · 07-06-2007, 05:59 PM

I'm sorry it's not clear from your sample what you want exactly?

Can E21 say

=IF(Q21="","",F21) copied down to E30

then B8 say

=IF(E21="","",E21) copied down to B17

**mike4545** · 07-06-2007, 06:51 PM

Sorry for the confusion. The objective is to search cells E21 to E30 and if there is data such as "Q21" in cell E21,in the sample, it would appear in cell B*. i hope this makes it clear. Please advise if not.

Mike

**oldchippy** · 07-07-2007, 05:37 AM

OK, try this, change the formula in E21 to

=IF(ISTEXT(Q21),F21,"") and drag down to E30, then in B8 put this

=IF(ISERROR(LOOKUP(2,1/(E21:E30<>""),E21:E30)),"",LOOKUP(2,1/(E21:E30<>""),E21:E30))

**mike4545** · 07-07-2007, 07:39 PM

Hi

Thanks for all the reply's. I have to go to a BBQ tonight but wiil work on it tomorrow and let you know.

Thanks again

Mike

**oldchippy** · 07-08-2007, 12:26 PM

Have a good BBQ, wait to hear from you

**mike4545** · 07-09-2007, 05:27 PM

Hi

Sorry for the delay in getting back to you. It works, it works!!! Thank-you so much. I would buy you 1, 2 3... beers if you were here. Well I've learned some more about Excel and what it can do.

Thanks again.

Mike