ignoring blanks cells while importing data

Hi,
I have been importing data from another excel sheet. I use ='C:\pc ranch
report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to
let it automatically copy all the data from the other excel sheet.

However, The data from the other excel sheet is automatically generated, so
it might have different number of columns. That's why I dragged the cells
down a few more rows than the original excel file has to leave extra space
just in case there might be more columns generated in the next generation.
The extra space that I dragged shows 0 or #REF.

Can I add something in front of the ='C:\pc ranch
report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells
won't show up as 0 or # Ref??
Thank you so much!!!

Dave
Tue Oct 07 07:37:25 CDT 2008

If you open the CSV file in excel, then you can use your formula (dropping the
drive/path):

='ca_outputs_yearly'!A46

If the sending cell (A46 in this case) is empty, then you'll see a 0.

You can hid that with a formula like:

=if('ca_outputs_yearly'!A46="","",'ca_outputs_yearly'!A46)

But you have to open the CSV file first. CSV files are plain text. They don't
have worksheets (or names) or cells (or columns and rows) until they're open.

=====
But since you're opening the CSV file, you may find it easier to just copy|paste
the data instead of using formulas.


yak141 wrote:
>
> Hi,
> I have been importing data from another excel sheet. I use ='C:\pc ranch
> report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to
> let it automatically copy all the data from the other excel sheet.
>
> However, The data from the other excel sheet is automatically generated, so
> it might have different number of columns. That's why I dragged the cells
> down a few more rows than the original excel file has to leave extra space
> just in case there might be more columns generated in the next generation.
> The extra space that I dragged shows 0 or #REF.
>
> Can I add something in front of the ='C:\pc ranch
> report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells
> won't show up as 0 or # Ref??
> Thank you so much!!!

--

Dave Peterson

dmoney
Tue Oct 07 10:47:02 CDT 2008

don't drag the cells down until you know how many rows you need to go. You
can determine that using the following:

Dim lr As Long
lr = Cells(65536, 1).End(xlUp).Row 'assign last row of data to variable
Selection.AutoFill Destination:=Range("A46:" & "A" & lr) ' fill down using
variable

HTH
dmoney

"yak141" wrote:

> Hi,
> I have been importing data from another excel sheet. I use ='C:\pc ranch
> report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to
> let it automatically copy all the data from the other excel sheet.
>
> However, The data from the other excel sheet is automatically generated, so
> it might have different number of columns. That's why I dragged the cells
> down a few more rows than the original excel file has to leave extra space
> just in case there might be more columns generated in the next generation.
> The extra space that I dragged shows 0 or #REF.
>
> Can I add something in front of the ='C:\pc ranch
> report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells
> won't show up as 0 or # Ref??
> Thank you so much!!!