MrExcel Publishing
Your One Stop for Excel Tips & Solutions

When ref a cell in another wkbook, how do i make no entry blank?


Posted by steve on June 06, 2001 2:37 PM

I am using this =[QE_Status_Data_Boeing_North.xls]qryQE_Response_Log_Boeing_North!A2 How can i get an empty cell to come back blank or say "empty"?
--Thanks


Posted by Mark W. on June 06, 2001 2:44 PM

Is zero (0) in the domain of cell A2? If not,
you could format the cell containing this formula
as...

[=0]"empty"

or

[=0]""

Posted by steve on June 06, 2001 3:01 PM

It needs to be in the formula that i gave because...

The formula is on one page. the page that it is getting the info off on is a sheet exproted to access and is deleated every update.

Posted by Aladin Akyurek on June 06, 2001 3:07 PM

Or, compute twice

If A2 is empty, you must be getting 0. Right?
A real blank when appropriate instead of zero would rquire computing twice as in:

=IF(ISBLANK('[tsdata v1.xls]BBoard'!$G$2),"",'[tsdata v1.xls]BBoard'!$G$2)

Aladin

================


Posted by Mark W. on June 06, 2001 3:10 PM

Re: It needs to be in the formula that i gave because...

I understand, but the question is what value does
this formula return if there's no value? 0? If so,
and 0 isn't in the domain of this data element then
why not hide the 0 value with the suggested number
formats?

Posted by steve on June 06, 2001 3:52 PM

How do i do it all in one cell?


How do i do this all in one cell?

Posted by Aladin Akyurek on June 06, 2001 4:11 PM

Re: How do i do it all in one cell?

Well, you need to change your own formula to:

=IF(ISBLANK('[QE_Status_Data_Boeing_North.xls]qryQE_Response_Log_Boeing_North'!A2),"",'[QE_Status_Data_Boeing_North.xls]qryQE_Response_Log_Boeing_North'!A2)

I'd use Mark's suggestion to avoid computing twice as above, even if the cost is small in this case.

Steve - I'm a bit surprised by the question quoted above. Are we missing something?

Aladin