Identifying non-returning formula (blank Cell)

[Vibeke]

Hi, I am using this formula, which is hopefully self-explanatory
in B7...
=IF(ISBLANK(C7)," ",IF(SEARCH(C7,$B$1:$H$1,1),OFFSET($B$1:$H$1,1,0),""))

My problem is that C7 contains a formula (which returns a blank cell if
conditions aren't met). I assume ISBLANK thinks this formula means C7 isn't
blank. Is there another function I can use to get B7 to return a blank
result if C7 is also blank?

Many thanks
Vibeke

 

[Max]

=IF(ISBLANK(C7)," ", ..

to: =IF(C7="","", ..

Any good? hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Vibeke]

Hi Max,
Thanks, I tried that but for some reason it returns #VALUE! I am not sure
why. Any ideas?
I guess I could insert another coloumn and then use ISERROR, but it seems
clunky.
Thanks
Vibeke

 

[Max]

Maybe you need TRIM ..
Try: =IF(TRIM(C7)="","", ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Vibeke]

Hmmm. Never used TRIM before, so it's nice to know about it, but I couldn't
make it work either. I should have mentioned, I am using SEARCH because C7
contains text and a date (and is based on a VLOOKUP) - so it's not just
straight text.

Many thanks for your suggestions.

 

[Max]

Couldn't help but feel that the scope has since crept beyond your original
issue, re:

Is there another function I can use to get B7 to return a blank result if C7 is also blank?

Try
=IF(TRIM(C7)="","",IF(ISERROR(SEARCH(C7,$B$1:$H$1,1)),"",OFFSET($B$1:$H$1,1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Vibeke]

Used properly, TRIM had rid me of #VALUE! but is not producing any results.
The formula in Column C is an ISERROR and VLOOKUP combination, so it will
appear blank the VLOOKUP doesn't find anything, and will present text and a
date if it does.

The formula for the B column is to check whether the formula in the adjacent
C cell has produced a visible result, and if so to compare it to the range in
B1to H1....

Oh....drat, drat, drat. I've just realised that in my efforts to simplify
things, I've totally screwed up my explanation and you've thus been looking
for a completely different solution. B1:H1 is in another spreadhseet. This
was the original formula, which I shoudl have posted in its entirity

=IF(ISBLANK($C7),"
",IF(SEARCH($C7,TaskAllocation!$B$1:$H$1,1),OFFSET(TaskAllocation!$B$1:$H$1,1,0),""))

I'm sorry
Vibeke

 

[Max]

Nothing further to add. You could use the earlier, adapted to suit for the
different sheetname involved. Maybe others could offer you better.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Vibeke]

Thanks, I had tried that. Many thanks for your efforts.

 

[Pete_UK]

Post the formula that you have in C7.

Pete

Thanks, I had tried that.  Many thanks for your efforts.





- Show quoted text -