| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying non-returning formula (blank Cell)
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
On the face of it, try changing your front IF check from:
=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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
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" wrote: On the face of it, try changing your front IF check from: =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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
Maybe you need TRIM ..
Try: =IF(TRIM(C7)="","", ... -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vibeke" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
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" wrote: Maybe you need TRIM .. Try: =IF(TRIM(C7)="","", ... -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vibeke" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
Couldn't help but feel that the scope has since crept beyond your original
issue, 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
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" wrote: Couldn't help but feel that the scope has since crept beyond your original issue, 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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
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 --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
Thanks, I had tried that. Many thanks for your efforts.
"Max" wrote: 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 --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying non-returning formula (blank Cell)
Post the formula that you have in C7.
Pete On Sep 4, 5:02*am, Vibeke wrote: Thanks, I had tried that. *Many thanks for your efforts. "Max" wrote: 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 ---- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula returning a blank cell if answer is zero | Excel Discussion (Misc queries) | |||
| returning blank when reference cell is blank | Excel Worksheet Functions | |||
| Returning Value of Next Non-Blank Cell in Column | Excel Discussion (Misc queries) | |||
| Identifying first non blank cell in 3 different columns | Excel Discussion (Misc queries) | |||
| returning blank cell | Excel Worksheet Functions | |||
Linear Mode
