#1   Report Post  
Brian
 
Posts: n/a
Default isblank function

Hello,

In cell A2 i have the following: =if(isblank(A3),A1,"")

Problem is that A3 has a formula within it and at times appears blank
because it contains a vlookup function that has not returned data yet because
its reference cell may is blank. So I thought that A3 could be considered
blank until such time that the reference cell activates the vlookup and
therefore data in cell A3.
What can be done to the above formula so that i get the same affect.

Thank You
Brian

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Hello,

In cell A2 i have the following: =if(isblank(A3),A1,"")

Problem is that A3 has a formula within it and at times appears blank
because it contains a vlookup function that has not returned data yet

because
its reference cell may is blank. So I thought that A3 could be considered
blank until such time that the reference cell activates the vlookup and
therefore data in cell A3.
What can be done to the above formula so that i get the same affect.

Thank You
Brian



  #3   Report Post  
Brian
 
Posts: n/a
Default

Perfect!
But what is LEN?

Thanks bob

"Bob Phillips" wrote:

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Hello,

In cell A2 i have the following: =if(isblank(A3),A1,"")

Problem is that A3 has a formula within it and at times appears blank
because it contains a vlookup function that has not returned data yet

because
its reference cell may is blank. So I thought that A3 could be considered
blank until such time that the reference cell activates the vlookup and
therefore data in cell A3.
What can be done to the above formula so that i get the same affect.

Thank You
Brian




  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

Brian,

It tests the length of the value in the cell, So, if it is blank, or if
there is a value that resolves to "", it will return 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Perfect!
But what is LEN?

Thanks bob

"Bob Phillips" wrote:

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Hello,

In cell A2 i have the following: =if(isblank(A3),A1,"")

Problem is that A3 has a formula within it and at times appears blank
because it contains a vlookup function that has not returned data yet

because
its reference cell may is blank. So I thought that A3 could be

considered
blank until such time that the reference cell activates the vlookup

and
therefore data in cell A3.
What can be done to the above formula so that i get the same affect.

Thank You
Brian






  #5   Report Post  
Brian
 
Posts: n/a
Default

Thank You


"Bob Phillips" wrote:

Brian,

It tests the length of the value in the cell, So, if it is blank, or if
there is a value that resolves to "", it will return 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Perfect!
But what is LEN?

Thanks bob

"Bob Phillips" wrote:

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" wrote in message
...
Hello,

In cell A2 i have the following: =if(isblank(A3),A1,"")

Problem is that A3 has a formula within it and at times appears blank
because it contains a vlookup function that has not returned data yet
because
its reference cell may is blank. So I thought that A3 could be

considered
blank until such time that the reference cell activates the vlookup

and
therefore data in cell A3.
What can be done to the above formula so that i get the same affect.

Thank You
Brian









  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Bob Phillips" wrote...
It tests the length of the value in the cell, So, if it is blank, or if
there is a value that resolves to "", it will return 0.


Give 'em a fish, and they'll ask for chips. Teach 'em to fish, and they can
go into business for themselves. Or perhaps using online help is only meant
for us archaic types.


  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default

You know that's the truth Harlan, it's the modern world :-)

BTW the phrase we (I?) use is give a mouse a cookie, and he'll ask for a
glass of milk ... :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
It tests the length of the value in the cell, So, if it is blank, or if
there is a value that resolves to "", it will return 0.


Give 'em a fish, and they'll ask for chips. Teach 'em to fish, and they

can
go into business for themselves. Or perhaps using online help is only

meant
for us archaic types.




  #8   Report Post  
Biff
 
Posts: n/a
Default

Hi!

LEN() is the function that returns the length of a string
in a cell.

=LEN(1234) = 4
=LEN( 1234) = 5

A cell is not blank if it contains a formula even if the
formula itself returns "". The "" is actually a zero
length text string.

=IF(LEN(A3)=0,A1,"")

So the formula is testing the length of cell A3.

Biff

-----Original Message-----
Perfect!
But what is LEN?

Thanks bob

"Bob Phillips" wrote:

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Brian" wrote in

message
news:25542EC7-BF35-40B3-BCA2-

...
Hello,

In cell A2 i have the following: =if(isblank

(A3),A1,"")

Problem is that A3 has a formula within it and at

times appears blank
because it contains a vlookup function that has not

returned data yet
because
its reference cell may is blank. So I thought that A3

could be considered
blank until such time that the reference cell

activates the vlookup and
therefore data in cell A3.
What can be done to the above formula so that i get

the same affect.

Thank You
Brian




.

  #9   Report Post  
Brian
 
Posts: n/a
Default

Thank You


"Biff" wrote:

Hi!

LEN() is the function that returns the length of a string
in a cell.

=LEN(1234) = 4
=LEN( 1234) = 5

A cell is not blank if it contains a formula even if the
formula itself returns "". The "" is actually a zero
length text string.

=IF(LEN(A3)=0,A1,"")

So the formula is testing the length of cell A3.

Biff

-----Original Message-----
Perfect!
But what is LEN?

Thanks bob

"Bob Phillips" wrote:

=IF(LEN(A3)=0,A1,"")

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Brian" wrote in

message
news:25542EC7-BF35-40B3-BCA2-

...
Hello,

In cell A2 i have the following: =if(isblank

(A3),A1,"")

Problem is that A3 has a formula within it and at

times appears blank
because it contains a vlookup function that has not

returned data yet
because
its reference cell may is blank. So I thought that A3

could be considered
blank until such time that the reference cell

activates the vlookup and
therefore data in cell A3.
What can be done to the above formula so that i get

the same affect.

Thank You
Brian




.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 12:25 AM
Is there a function for "not isblank" (find a cell that has a val. Jim Excel Worksheet Functions 3 December 8th 04 07:29 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 07:07 AM
change function variable prompts?? thinkingfield Excel Worksheet Functions 1 November 8th 04 04:01 PM
Counting Function Dilemma Simon Lloyd Excel Worksheet Functions 0 November 8th 04 03:13 PM


All times are GMT +1. The time now is 09:43 AM.

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

About Us

"It's about Microsoft Excel"