Protecting Data When Extracting From Another Sheet
Feb 17, 2010
I need is for the Roster sheet, which has the exact same column and row headings as the Leave sheet, to read the data from the Leave sheet and change dynamically if the Leave sheet is changed. If there is data in any cell then I need to protect just that cell in the Roster sheet so that the user creating the staff roster is unable to edit it out.
I have created a plain cut and paste macro on open from Leave to Roster but this doesn't give me the dynamic linking, and doesnt help solve the problem of needing to check each and every cell in Roster against a corresponding cell in Leave, but also if its blank in Leave to allow the user to update that cell in the Roster sheet.
View 3 Replies
ADVERTISEMENT
Dec 11, 2011
What is the best way to protect a Data sheet, but still be able to use a FrontEnd Userform.
View 4 Replies
View Related
Mar 9, 2014
I have the attached file with two worksheets D30(1) and Sheet 2.
I need to extract some of the information contained on the D30(1) sheet into the format as contained on Sheet 2. It is almost like a transpose but because all of the information is not needed and all cells don't have info in it, I didn't even try to transpose the D30(1) info.
Is there an easy way to extract the information from sheet D30(1) to present it in the format of Sheet 2? I need to do this for approximately 50 sheets similar to D30(1). Some sheets might even have more columns than the example provided.
View 4 Replies
View Related
Apr 1, 2014
I'm attaching the test file with one example sheet. Within the same sheet, there is month end dates in column A, and from column B till column E, there are 4 variables (Foreign LT Debt, Local LT Debt,Foeign ST Debt and Local ST Debt). Their respective month end values are already given from column G till Column R. How it is going to be extracted is that against each effective date for that variable (Foreign LT debt, for example), the Rating is given under Rating header. So, for instance, for Foreign LT debt, rating from 25/8/1993 till 4/2/1997 is BB-. So, for all CELLS in column B between these dates, the rating should be BB-. Before that, since there is no value given, I want to put it NA. I have filled the values in column B for demonstration. I want to write a function or perhaps a macro to do that automatically for these 4 variables. There are 50 + sheets with the same layout, so I hope the macro or look up function have not to be written 50+ times.
View 1 Replies
View Related
Jan 17, 2014
I'm not sure if this is best handled in the Formulas & Functions section or elsewhere, but this is my best guess. I have a spreadsheet used for tracking hardware issues with data in two worksheets:
1. PageCount
Column 1: Date
Column 2: Serial#
Column 3: PageCount
2. Tickets
Column 1: Date
Column 2: Serial#
Column 3: TicketNumber
What I'm trying to do is find a way to extract from those two sheets the date, serial number, page count, and ticket number where the date / serial # are the same and dump this to a new sheet. I've thought that I may need to concatenate the date / serial number together into a single cell or something, but I'm really at a loss as to how to pull this data. Part of me thinks this should be done in SQL, not a spreadsheet, but that's how our reports are produced.
View 1 Replies
View Related
Apr 26, 2013
I want to create a sort of complex logging system for my work.
As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.
View 4 Replies
View Related
Feb 3, 2014
Extract data from one sheet to another sheet then transpose automatically.
I have attached the work book of what I'm trying to accomplish.
View 14 Replies
View Related
Apr 13, 2012
I have two work sheets as-
Sheet1
idnameclassscores
1abc280
2efg276
3hij555
8klm478
9mno490
Sheet 2
nameschooldate of admission
abcpublic school2/9/2011
efgpublic school3/4/2010
hijprivate school5/9/2011
klmprivate school8/9/2011
mnoprivate school9/10/2011
now what i want is - on sheet 3 compiled data as-
idnameclassscoresschooldate of formation
View 1 Replies
View Related
Jul 16, 2013
I am working on a workbook in which I extract data from a SQL database into a sheet with more or less the following format:
ID_Company
ID_Level
ID_XX
[Code].....
The aim is to extract a list of any employee that has been tagged as "NEW" in the last column, with his/her detail as per the column headings - and place the resulting list in another sheet. The extract is done dynamically and varies from month to month. So in other words the column headings 2013.MAY and 2013.JUN will change next month to 2013.JUN and 2013.JUL respectively - and so would the detail of the data below them as well. The "NEW" tag is simply assigned through an IF statement.
I had a look at most of the Excel Magic trick video clips on YouTube and managed to get a data extract from my main data sheet - BUT was only able to do the extract on the SAME sheet and NOT onto another sheet in the same workbook.
View 3 Replies
View Related
Dec 31, 2013
I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.
I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.
I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)
View 7 Replies
View Related
Mar 9, 2009
I have written basic macros (probably not the most efficient solution, but its what I know how to do). Up to the present point things are going well. (I tried to attach file, but without success - So I will email the file directly it is pretty easy to figure out) I have option buttons tied to macros that populate a data sheet based upon the input.
This works out quite well and I am able to generate the statistics I desire quite easily. Now comes the second phase, (this is where I am stuck) - when I click the option button "no", I would like this to generate a line item in a separate existing worksheet which lists the item number, tells the discrepancy (as written in the column on the inspection criteria sheet), has a text field for the inspector to briefly explain the exact nature of the discrepancy, and also keeps discrepancies in proper order. Conversly, if "yes" is selected, I would like to be able to check the associated comment page, search the page for that discrepancy number, and if found, delete the line item, then re-oder the page.
View 8 Replies
View Related
May 9, 2014
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
View 2 Replies
View Related
Mar 24, 2008
I have a Sheet with some Macro Buttons for the boss to use.
How can I protect the sheet from any Data Entry changes, but still allow the Buttons to be used?
I tried a few scenarios and in all, the Buttons can't be used.
View 9 Replies
View Related
Jul 30, 2009
I have a sheet and want users to be able to type in a set of cells but not able to move the cell how do i do this?
View 9 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Mar 26, 2007
I have a workbook with particular worksheets that should never be deleted. If they are deleted by the user the workbook will become unusable.
The users need the freedom to edit data within the sheets (so protecting them is not an option), however, I need to restrict this freedom so that they cannot under any circumstance delete the sheet. Is there such code that I could use in the worksheet code to stop this?
View 12 Replies
View Related
Dec 6, 2006
way of protecting a very hidden sheet, I will still need to open the sheet myself everytime I use the program. The only person to open this very hidden sheet is myself.
View 9 Replies
View Related
Oct 2, 2008
I want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.
View 9 Replies
View Related
Dec 20, 2008
I am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.
View 9 Replies
View Related
Dec 23, 2009
Excel 2002
I have a sheet that has some cells already protected by a password. I want another person to be able to make changes to the unprotected cells and then be able to push a button (macro) that will then unlock the sheet activesheet.unprotect ("mypassword"), change all the cells to "locked" and then have the protect sheet window pop open so that they can type in their own password and save it. Is that possible?
View 9 Replies
View Related
Jan 7, 2009
i have produced a spreedsheet on ms excel.
On sheet 2 is all of the information and tables etc.. for example prices and information for the combo boxes an such...i need to protect this sheet so no one can change anything on it
the problem is when i do so, and go back onto sheet 1 and say tick a checkbox or select something from the combo box is doesnt allow me to, the protection message comes up
i dont want this to come up on sheet 1, only on sheet 2!
i think its because i have links between the pages? like all of the info for the combo box is on sheet 2
View 9 Replies
View Related
Sep 26, 2013
I need to protect a sheet with following access to users.
1. data can be entered in column a only... rest is automatically updated with formulas
2. users can insert/ hide rows. but can not delete any rows and can not hide/ insert/ delete columns
View 4 Replies
View Related
Oct 27, 2009
Is there a way to lock out a spread sheet if it is sent to an unauthorized e-mail address or forwarded to another e-mail address?
View 9 Replies
View Related
Feb 14, 2007
A1 is number of days available
this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated.
A2 has starting date
B2 has ending date
C2 is days between these two dates
D2 is number of days left i.e. A1-C2
I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
View 3 Replies
View Related
Jul 19, 2014
I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.
However, each file the first tab called "Clients" has information that I want protected so users can't alter it. I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.
What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.
View 5 Replies
View Related
Nov 12, 2009
Macros are just not my strong suit. I can usually find my answers here but this one has me stumped. I have rewritten this thing everywhich way but loose and this is my latest version.
Sub SetProtect()
'
' protect Macro
' prompts for password protection
'
' Keyboard Shortcut: Ctrl+p
'
Dim Response As String
Dim Answer As Integer
SetProtect:
Response = InputBox("Enter password")
If Response = "safety2" Then.......
View 9 Replies
View Related
Mar 26, 2012
I am building an Excel workbook for use by many users in a company. The Worksheet that all users will be able to see and access will reference a Data tab that contains sensitive data. I know how to hide and password protect that tab, but just wondering if anyone can tell me how secure that is? In other words, if someone really wanted to get that data, how easy is it for the password to be broken? I will need to prove to management that the data is secure.
View 6 Replies
View Related
Dec 5, 2012
I have a cell in my spreadsheet (J3) that I need to never be changed or else other macros in my workbook will not work properly. I decided to try and use data validation since it is easy to change the rule every month and I can stay away from messing with passwords.
My cell value is a date, so I set data validation to have that cell equal only that specific date. If a user tries to overwrite the cell value, the error messages is prompted correctly. The one BIG problem is that I can just press the delete key and delete the contents of the cell. Is there any way of preventing this from happening without having to lock the cell and password protect the sheet?
View 3 Replies
View Related
Oct 15, 2008
I have over 40 workbooks that have a summary page for the products contained within each. I would like to extract the information on the summary page to an html format with all the html tags so that I may post the information within a sharepoint webpart.
I have the publication the sharepoint down, but the extraction to html is my issue.
The information is contained within a 2 column by 15 row section give or take a row depending on the workbook. I am not to worried about the format of the cells or sheet, just the content.
View 9 Replies
View Related
Sep 1, 2009
Yes, I am a newbie to VBA. I've tried for hours now to come up with code to make this work, but no go.
Here's the situation:
There are a list of cells all in column A (row ? - ???) that have a string that needs to be searched to locate a number, and insert that particular number into a cell in another sheet, which is in another workbook.
These are actual examples:
*001 RJ 17 17 1,436 316 28 17 0 0 0 0 0 0
*042 RJ 39 39 215 215 0 39 0 0 0 0 0 0
*03A RJ 8 8 282 202 2 8 0 0 0 0 0 0
The numbers needed from these examples would be: 1,436
215
282
End results:Trip 001 = 1,436
Trip 042 = 215
Trip 03A = 282
FYI...The cell data always starts with *, and is followed by three characters we refer to as a Trip, then a space, and then RJ.
The column with the data is always "A", but the starting row may not always be the same. The rows continue down one by one, but there may not always be the same number of rows (each row used will ALWAYS start with an asterisk, though.)
The number that needs to be extracted is always between the 4th and 5th spaces - CHR$(32)'s.
The numbers, once found, then need to go in another sheet into one of the following cells - C28, C29, C30 or C31. Which cell the number goes into depends on which one of the cells, C28-C31, matches the three character Trip text shown after the *. There are currently 16 different sheets that need to be searched to find where the matching Trip number is. The sheet names do not change.
and yes, the people always enter everything shown in the example into one cell.
View 9 Replies
View Related
