Forum Discussion
import data to a protected worksheet
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 🙂
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