Cell References data that doesn't exist?

Excel counts each day as one, so 1/0/1900 is the date equivalent of 0. You can suppress 0s in Options (depending on the sheet, this may not be recommended if you do want to show some 0s), use conditional formatting to make 0s appear the same color as your background (making them invisible), or use =IF('2008 Apartment Comps (INPUT)'!AC27="","",'2008 Apartment Comps (INPUT)'!AC27)

Dates in Excel are days counted from Jan 0, 1900 (31 Dec 1899), so zero formatted as a date looks like that.

Originally Posted by darkyam

Excel counts each day as one, so 1/0/1900 is the date equivalent of 0. You can suppress 0s in Options (depending on the sheet, this may not be recommended if you do want to show some 0s), use conditional formatting to make 0s appear the same color as your background (making them invisible), or use =IF('2008 Apartment Comps (INPUT)'!AC27="","",'2008 Apartment Comps (INPUT)'!AC27)

could you possible describe the syntax and arguments for your code? i presume that the "","" part means empty?

Yes, "" outputs a blank cell. The IF function takes 3 arguments, a logical test, a value to return if true, and a value to return if false. The first argument calls the value in AC27 and compares it to "". If it is blank, it returns a blank to the cell in which the formula resides. If it is not blank, it returns the value of AC27.

Were you / are you a Hokey?

thanks, that seems to have worked perfectly.

one more quick question though. If i reference a cell with multiple lines of text in another worksheet using this method (or any that i've tried) it converts it all to one line. is there a way to keep the text in lines? like to keep the returns there?

EDIT: no sorry it's the name i use for everything. it's my aim sn and hockeyplyr48 was taken. i'm too lazy/not creative enough to think of a new sn/online tag

Not that I'm aware of, especially not without VB script (which I'm unfortunately not good enough to provide). Excel does not do well with break characters (tabs, returns, etc.) within cells.

Originally Posted by hp48

is there a way to keep the text in lines? like to keep the returns there?

The line feeds are there; just format the cell in which the formula appears to wrap text.

Originally Posted by darkyam

... especially not without VB script

VBScript is a scripting language used (mostly, I think) in Internet Explorer; VBA is Excel's automation language.

Originally Posted by shg

The line feeds are there; just format the cell in which the formula appears to wrap text.

You mean just shrink the cell so the text wraps? If so it won't work like that because these are addresses. It deletes the return/space between the first and second line so that it becomes one word. For example, Myhouse4893 Main StreetRaleigh, NC 99999. It would break it all up incorrectly.

If that's not what you meant could you explain?

VBScript is a scripting language used (mostly, I think) in Internet Explorer; VBA is Excel's automation language.

I was referring to a script written in VB, which I have heard used almost interchangeably with VBA when talking about macros in Microsoft applications. However, point taken; I will try to be a little more precise in the future to avoid confusion.

Maybe we've gotten disconnected.

Enter

Myhouse
4893 Main Street
Raleigh, NC 99999

all in cell A1, with the line feeds entered using Alt+Enter.

In B1, enter =A1, and format B1 to wrap text. If you make column B wide enough, you'll see the line breaks in the same position.

My point was, the formula does not delete the line feeds.

ahh i see what you're saying. that fixed my problem. Thank you everyone for helping me, I greatly appreciate it!