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!!! |
ignoring blanks cells while importing data
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_year ly'!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 |
ignoring blanks cells while importing data
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!!! |
All times are GMT +1. The time now is 12:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com