+ Reply to Thread
Results 1 to 4 of 4

Fixed cell formula links to pending Imported *.csv data is obliterated (#REF) post import

  1. #1
    Registered User
    Join Date
    10-11-2016
    Location
    Cincinnati, OH
    MS-Off Ver
    2013
    Posts
    3

    Fixed cell formula links to pending Imported *.csv data is obliterated (#REF) post import

    Hi all,
    (newbie Forum Member here)

    Hoping to get some help with a problem I'm having.

    I have a new laboaratory instrument that puts out test results in *.csv format. I created an excel template with a summary report format on one tab within the workbook, with the intention of having this workbook be a blank template (e.g., operator opens the file template, does a "save as..." new file name and then steps through the process for getting the instrument's *.csv data imported. Post data importing, I want to have the template report out Key data/information needed from the imported file(s).

    I predetermined (mapped out) which cell references I needed to extract the values I want (from the intrument's standardized *.csv file output), I setup several tabs for importing data into for each new test condition; I then created a summary report tab and wrote the formulas to go get values from each of the import data tabs, referencing what should be 'fixed' cell references to get the data needed.

    To summarize, I want to be able to extract key data/information from this (instrument output) data so as to be able to report out results in a more concise/comprehensive manner to be able to give to my customers. The problem is, when I actually IMPORT the *.csv file data my 'fixed' cell references are obliterated (changed to #REF! post-importing the dataset).

    EXAMPLE: The formula reference "COND_01" is the name of the worksheet where I intend to import the first condition's (*.cvs file) test results.

    =IF(COND_01!$B$6="","",COND_01!!$B$6) ...post-importing any given *.csv file import into tab COND_01 the formula changed to =IF(COND_01!#REF="","",COND_01!!#REF)


    Any suggestions on how I can get this to work?

    Thanks,
    Michele

    P.S. I have no problems in import this "From Text" data into a separate (pre labeled) tab within an excel spreadsheet (template) I created.

    I click on the tab where I want to import the test data into and click on the first cell A1. Then, I go to the Data tab on the menu bar; select Get External Data option and From Text and then I select the *.csv file I want and hit the Import button. I leave the Import Wizard's default settings it opens with as "Delimited", the Start import at row "1", file origin is "65001 : Unicode (UTF-8)"; and click the Next button; in the next window, I change the delimiters from Tab to Comma, and click the Next button; I leave the Column data format as "General" and click the Finish button; and click OK to confirm that I want the data to be put into the existing worksheet I've selected and starting at $A$1 cell.

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Fixed cell formula links to pending Imported *.csv data is obliterated (#REF) post imp

    Hi Michele,

    Welcome to the forum.

    Your problem is due to the addresses being lost when you paste in the new data.

    Try this: =IF(INDIRECT("COND_01!$B$6")="","",INDIRECT("COND_01!$B$6"))

    The INDIRECT formula will not be affected by the cut and paste of new CSV data.

    The DISADVANTAGE is that it cannot be copied down or across because is stores the whole formula as text.

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  3. #3
    Registered User
    Join Date
    10-11-2016
    Location
    Cincinnati, OH
    MS-Off Ver
    2013
    Posts
    3

    Re: Fixed cell formula links to pending Imported *.csv data is obliterated (#REF) post imp

    David,
    This was exactly what I needed. Problem SOLVED!
    Thank you for taking the time to help.
    Regards,
    Michele

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Fixed cell formula links to pending Imported *.csv data is obliterated (#REF) post imp

    You're welcome Michele.

    Please take the time to mark the thread as SOLVED.

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-15-2014, 01:59 PM
  2. macro to import varying data rows into fixed table
    By aselly22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2014, 12:51 AM
  3. [SOLVED] Macro to Selectively Import Fixed Length Data into Excel
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-24-2014, 12:06 AM
  4. [SOLVED] Formula (not vba) to delete row if cell blank on imported data
    By NeilAZ in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-05-2013, 02:37 AM
  5. repeated import of similar fixed width data files
    By riwiseuse in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-25-2013, 10:50 AM
  6. how to break links wih data imported into Excel
    By jagguy in forum Excel General
    Replies: 2
    Last Post: 04-30-2008, 07:10 PM
  7. Replies: 1
    Last Post: 08-04-2006, 11:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1