A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

differentiating between formulas and values



 
 
Thread Tools Display Modes
  #1  
Old July 7th 05, 02:31 AM
shellshock
external usenet poster
 
Posts: n/a
Default differentiating between formulas and values


I have a bunch of values in column A. In column B, I'm using a
user-defined function that processes the values in column A and returns
either text of some sort or a blank.

However, even though the cells in column B appear to be unique -- well,
except for the blank cells -- the formula in each cell is essentially
identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.).

I want to do an operation only on those cells in column B that are
displaying text, but I don't know how to do that automatically.

Is there a way to somehow differentiate between cells that return a
"tangible" value, and those that return a "blank" value?


--
shellshock
------------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935
View this thread: http://www.excelforum.com/showthread...hreadid=385124

Ads
  #2  
Old July 7th 05, 03:17 AM
ScottO
external usenet poster
 
Posts: n/a
Default

One way that should work ...

If you use Auto Filter to filter out the Blanks, then you can select your
range in ColB and use Select Special Visible Cells Only and then operate on
the Tangible cells.

Rgds,
ScottO

"shellshock" > wrote
in message ...
|
| I have a bunch of values in column A. In column B, I'm using a
| user-defined function that processes the values in column A and returns
| either text of some sort or a blank.
|
| However, even though the cells in column B appear to be unique -- well,
| except for the blank cells -- the formula in each cell is essentially
| identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.).
|
| I want to do an operation only on those cells in column B that are
| displaying text, but I don't know how to do that automatically.
|
| Is there a way to somehow differentiate between cells that return a
| "tangible" value, and those that return a "blank" value?
|
|
| --
| shellshock
| ------------------------------------------------------------------------
| shellshock's Profile:
http://www.excelforum.com/member.php...o&userid=24935
| View this thread: http://www.excelforum.com/showthread...hreadid=385124
|


  #3  
Old July 7th 05, 03:28 AM
Earl Kiosterud
external usenet poster
 
Posts: n/a
Default

Shell,

In Excel, there isn't any Tangible property, and we get together once a week
and communally lament that there is no NULL value that can be returned from
a formula or function. But you can test for the UDF returning text with:

=ISTEXT(A2)

Or look for a zero-length string with

=LEN(A2)=0

=IF(LEN(A2)>0, "There's a string", "Empty string")

Or look for a number:

=ISNUMBER(A2)
--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"shellshock" > wrote
in message ...
>
> I have a bunch of values in column A. In column B, I'm using a
> user-defined function that processes the values in column A and returns
> either text of some sort or a blank.
>
> However, even though the cells in column B appear to be unique -- well,
> except for the blank cells -- the formula in each cell is essentially
> identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.).
>
> I want to do an operation only on those cells in column B that are
> displaying text, but I don't know how to do that automatically.
>
> Is there a way to somehow differentiate between cells that return a
> "tangible" value, and those that return a "blank" value?
>
>
> --
> shellshock
> ------------------------------------------------------------------------
> shellshock's Profile:
> http://www.excelforum.com/member.php...o&userid=24935
> View this thread: http://www.excelforum.com/showthread...hreadid=385124
>



  #4  
Old July 7th 05, 06:02 PM
shellshock
external usenet poster
 
Posts: n/a
Default


Scott, that's a neat idea with the autofilter, although I would need to
write a macro in order for that to happen automatically. (My file is
updated on an ongoing basis by several people, and I wouldn't want to
have to do that operation manually.)

Earl, I didn't even know about the LEN function until just now -- and
I think I was only vaguely aware of the ISTEXT function. In the end, I
did something very much along those lines: (=IF(B1<>"", *my operation*,
""))

Thanks very much to both of you.


--
shellshock
------------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935
View this thread: http://www.excelforum.com/showthread...hreadid=385124

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
#N/A Values : Returned by Formulas vs Entered Manually monir Charts and Charting in Excel 8 July 7th 05 01:16 AM
How do I sum rounded values from two seperate formulas in Excel? Federali Excel Worksheet Functions 1 May 3rd 05 06:01 PM
How do I clear a worksheet of values, but leave formulas intact? [email protected] Excel Worksheet Functions 2 April 18th 05 05:53 PM
display values of formulas in Excel Binyaaust Excel Worksheet Functions 1 April 14th 05 12:35 PM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 01:15 AM


All times are GMT +1. The time now is 07:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.