Eliminate blank entries in linked Excel file 2

[rgreen511]

I have an Excel file that links to another file and grabs data using a simple link, no macros.

The entries are of the format (ex):

11/27/07 12.3

Several of the entries are blank, and the blanks screw up my equations that are using the data (calculating moving ranges, etc.).

I'm looking for a way to eliminate these blank lines, but still maintain previous changes I've made, such as excluding points, or changing the values of points. I thought of pulling the data into a different sheet, eliminating the blanks, and then copying the cleaned up version to the main sheet. However, this copy and paste will overwrite the previously mentioned modifications.

I'm not afraid to use macros or VB, I just couldn't think of an easy way to do this. Thanks.

 

[AnotherHiggins]

For a VBA solution, please create a new thread in forum707.

To keep empty cells from appearing as zeros on your sheet, use an if statement, such as:

=If([Book1]Sheet1!$A$1 = "", "", [Book1]Sheet1!$A$1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

You could use MS Query.

faq68-5829

Where [Your Date Column] Is Not Null

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[rgreen511]

Thanks. I already did have the if statement to prevent that, but certain equations still treat blanks as zeros. And graphs treat blanks as zeros as well.

I will try using MS Query. I'm fairly familiar with it, so I'll see what I can do.

 

[AnotherHiggins]

FYI: To keep graphs from charting zeros, return #N/A in your If statement. Do [!]not[/!] surround it with quotes.

It will look something like this:

=If([Book1]Sheet1!$A$1 = "", [highlight]#N/A[/highlight], [Book1]Sheet1!$A$1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.