Forum Discussion

JulreFVST's avatar
JulreFVST
Copper Contributor
Aug 20, 2021

import data to a protected worksheet

Hey,

 

I need to be able to import data from a txt file into an excel sheet which is protected, because some cells have formulas in them.

Is this possible in any way? Right now, when the sheet is protected, excel doesnt allow imports of data.

 

version is 2016

 

Thanks in advance 🙂

excel

4 Replies

Sort By
  • mathetes's avatar
    mathetes
    Silver Contributor
    Aug 20, 2021

    JulreFVST 

     

    Hey!

     

    There often are very good and lasting reasons to protect a spreadsheet and its formulas. Presumably this was created by somebody other than yourself (or else you'd be able to unprotect it and not need to ask).

     

    Do you need to import specifically into the very worksheet that is protected? Why not into a second (or third, whatever; a separate) worksheet in the same workbook. You can still access it and use the data.

     

    Is it possible to share the actual sheet, just rendering anonymous any confidential or proprietary data on it?

    • JulreFVST's avatar
      JulreFVST
      Copper Contributor
      Aug 21, 2021

      mathetes 

      Hello!

      Thanks for your answer.

       

      Yes it was created by my colleague and it has some very complex formulas, that need to be protected from other users.

      The reason why I want to import to this sheet directly, is because it is set up with another sheet, to calculate limits, do lookups etc.

       

      After giving it some thought, I think you are right. I will have to set up a new sheet, in which the data can be imported, followed by a macro button, which will copy paste it into the protected sheet.

      Do you have any other ideas to get imported data from a sheet into another sheet, other than creating a macro?

       

      I don't think I am allowed to do so, no 🙂

      • mathetes's avatar
        mathetes
        Silver Contributor
        Aug 21, 2021

        JulreFVST 

         

        I do 99% of my Excel work without resorting to macros. They can be useful, but I generally find that Excel has so many wonderful built-in functions that are far more efficient and error-free.

         

        I have, for example, a spreadsheet that tracks my options investments (Puts and Calls on the stock market). I export from my broker a file that lists all of the day's prices, along with various other bits of data (yearly highs, lows, ratios, etc). That comes as its own sheet. I import that data wholesale into its own sheet in my tracking workbook by means of the FILTER function. And from there individual data items are transferred to individual sheets tracking individual positions. To do that I use a variety of functions: INDIRECT, XLOOKUP, VLOOKUP are prominent among those. Here's a good resource for developing an understanding of them, if needed: https://exceljet.net/excel-functions/excel-xlookup-function

         

        Excel has many ways to take data from source sheet A and get it to cells in B, C, D... it can be done on a cell-by-cell basis using those LOOKUP functions.

         

        It can be done as a whole table or selected rows/columns from a table by means of FILTER, SORT and UNIQUE, these latter called Dynamic Array functions. Here's a video that explains them (you will need the most current version of Excel for them to work). https://www.youtube.com/watch?v=9I9DtFOVPIg

         

Resources