I have users filling in data in columns C and D, I need a macro which will select everything entered and lock those cells - well the catch is, if both columns have data. If column C or D are empty I need that row to remain unlocked. No sorting can occur as well.
View 2 RepliesHow can I password protect a certain range of cells (titled 'list'), that when someone tries to edit them, a password dialogue box appears, saying only bob can edit these cells please enter password
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder.
for example the range is O13:AX500
the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant"
The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks.
The total of the referenced values would then be placed in say B1....
I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process.
Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".
Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"
The example file explains it a lot better..
Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...
Is there a way to prompt users to enter information in a specific range of cells before they save?
View 1 Replies View RelatedIs there a way to just protect just a single cell, in order to lock & hide a formula within that cell, without having to password protect the whole worksheet?
View 9 Replies View RelatedI am working on both MS Office 2003 and 2007. I am currently working some formulas on the worksheet which I would like to be protected. Therefore I would like some cells in the sheet to be protected and therefore only the person knowing the password (administrator) will be able to change.
View 3 Replies View RelatedI have a spreadsheet with about 2000 lines of vba code. My application needs to be protected so users don't mess themselves up; however, I really only needs the contents of cells to be protected. I have many macros that run, and in order to do this I need to unprotect and re-protect the worksheets. My problem is this: when I re-protect the worksheets, everything is locked up. I would like to allow users to change column widths, hide and un-hide rows, etc. I can't find the parameters needed to do this more controlled type of locking. Is there a list somewhere of all the parameters that can be passed to the Protect method? Here is my current subroutine:
Sub ProtectSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
Next
End Sub
I would like to lock some collumns and yet change the formating on the unlocked cells mi other columns. Is there another way to protect cells other than locking cells and then protecting the worksheet?
View 5 Replies View RelatedI have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
it is making a well laid out and good sheet look like something a child has done
I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
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.
I have had a workbook that has 2 sheets.
When I protect the main sheet with a password, the unlocked cells are also locked.
When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.
When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.
I also am using Freeze Panes. Removed the Freeze and still no good.
If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.
If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
I have a spreadsheet that i use as a log for recording when certain tasks have been completed.
Each worksheet in the workbook represents one month. Within each month, there are monthly tasks and weekly tasks. Currently i manually unlock the relevant weekly cells at the beginning of each week, and lock them again at the start of the next week. Similarly with the monthly task cells.
Is there a way for Excel to automatically unlock the relevant cells based on date so i don't have to go through this task at the start of every week and every month?
I have certain cells in my spreadsheet that contain formulas linking them with other sheets within my workbook. I have grouped sets of rows in my first 2 sheets (to show just headings and to expand to subheadings & details). When I try protect the cells with formulas on the sheet then it blocks me from expanding or contracting the row groups.
Is there an easier way to stop someone editing or deleting the data in my cells? There are about 2 dozen cells with formulas in my first sheet that need protecting - unfortunately not in any particular order...
I've found many resources that tell me how to allow for the insertion/editing of comments within a protected worksheet. When I protect the worksheet, I can select "Edit objects" to allow anyone to insert comments.
However, my issue is that I'd like the Insert/Edit comment functionality to require a password, and this happens to be the opposite of what those resources instruct. I also want the content in those same cells to be editable by anyone.
I have a worksheet from which I regularly clear all contents by selecting all the rows and selecting 'Clear Contents' from the right button menu. However, I now have added a formula in Col Y that I want to protect. I know I could select all columns up thru X, but that would clear the R1 headers, so I want to avoid doing that. I can always work around, but I'm curious, is there a way to clear only values and thus protect the formulas when using the Clear Contents command?
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
I would like to know how i get excel to put information from say I7 into I94. i want it to be exactly the same information - and its not always a number. It could be a number, or text or a date?
View 2 Replies View RelatedI'm trying to extract information from a cell that I cut and paste from another source:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 441
Each cell is different in that it may be a different day or time:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 434
I want to make a formula that will extract the pertinent information (day of the week and time), and store it to another 2 cells, making a list as it "extracts":
Day
Time
Tuesday
8:00 - 9:15
Thursday
8:00 - 9:15
I have rows of cells with the following information..
Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523
Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.
Row 1, Column 1: Address
Row 1, Column 2: City/State
or ..
Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523
I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:
Can I use part of the data in a column of cells to sum data in a column.
E.g. I want to sum valves in Column V that match the Data in Column T by using the following cells CB 1A1x, CB 1A2x, CB 1a3x CB 1A5, etc etc CB 1B3x...... to obtain the totals
For CB 1A1, CB 1A2, CB 1A3 etc etc.
Capture.JPG
My goal is to create a pair of validation lists. The first will allow you to select what craft you are using (i.e. Alchemy). That will bring up the second list which will allow you select what recipe you are using (i.e. Black Ink). From here, I want the spreadsheet to automatically fill out what ingredients are in the recipe, the skill levels needed to complete the recipe at 100% success, and what the yield is each time successfully complete the craft.
The first part is easy, using the INDIRECT function to pull up a second list from the first. I have no idea how to make the other cells fill out, or even how to structure the data to make it accessible.
I have column A full of test numbers, and column D is revisions the tests show up in (4 different versions). I would bring this spreadsheet down from 1800 rows to around 500 rows by making the info on D appear on one line in columns E,F,G,H, rather than four rows.
Example:
This
_A__|__B__|__C__|__D__|
1001|_____|_____|v1.0
1002|_____|_____|v1.0
1002|_____|_____|v1.1
1002|_____|_____|v2.0
1003|_____|_____|v1.0
1003|_____|_____|v1.1
1004|_____|_____|v1.1
1005|_____|_____|v1.0
1005|_____|_____|v1.1
1005|_____|_____|v2.0
1005|_____|_____|v2.1...................
I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and only
the "c")
Joe Cheddar 1 2 1
Juan Valdez 1 3 2
Sam Vidal 1 1 2
Betty Juno 2 4 2
...there is only one "1" in a column. So if column 1 has three ones it doesn't return anything. Columns 2 and 3 though have only one occurance of "1." What I need returned is for column two, "Sam Vidal", and for column three, "Joe Cheddar." Column one would show "No Winner"
I have a sheet that has been created with a pivott table, and as such has a lot of blank cells in the description column, and I want to do a little macro to fill all of these. Basically, what I was thinking, was that the macro finds the last cell with data in it in column A. Then it moves up one cell and checks if its empty. If it is empty, then it copies the information from the cell immediatly below then moves up one and does it all again. If it is not empty then it just moves up one cell and does it all again.
View 3 Replies View RelatedI have the following code in a macro of mine which checks each cell has something entered and if not gives the message box.
View 5 Replies View RelatedMy question requires me to do a Vlookup question that needs to get the information in the next two cells over from the left most cell. In short I need to put info from two different cells from the same row and look up value into one cell.
View 2 Replies View RelatedI'm trying to fish information from one worksheet to another.... Here's my sub, what's my SUBcranial problem?
Private Sub bt_PopulatePage_Click()
' Inventory Data Transfer to Report Structured Page
Sheets("Liquor Data").Select 'Switch to Liquor Data Sheet
Dim x 'Line Numbers x = Liquor Data Line
For x = 2 To Last_Row("B") 'Start at the first Liquor Entry Line
Sheets("Liquor Data").Select
t_liq = Cells(x, 2) ' Liquor Name in Starting in B,2
t_Class = Cells(x, 4) ' Class starting in B,4
t_type = Cells(x, 5) ' Type Starting in B,5
t_dist = Cells(x, 8) ' Distributor Starting in B,8
Sheets("Inventory Form").Select 'Select Inventory Page
Cells(x + 7, 1) = t_liq
Cells(x + 7, 2) = t_Class
Cells(x + 7, 3) = t_type
Cells(x + 7, 4) = t_dist
Next x
End Sub