Prevent Repeat Values In Column Across Worksheets
Nov 23, 2013
I know how to use data validation to prevent repeat values entered into a column on one worksheet using data validation. I don't understand how to do this across 12 worksheets (months of the year) in a file.
To be more clear: When I enter an ID in a column cell, I want that to be compared to all the other ID's in all the past worksheets and rejected if it has already been entered
All are column D The values entered are identification numbers and have both letters and numbers.
View 5 Replies
ADVERTISEMENT
Feb 3, 2007
I am looking for a formula to repeat a given set of values, a certain number of times ...
See attached for visual explanation ...
View 7 Replies
View Related
Jan 30, 2014
I have data in rows like this
A B C
1 x y z
2 x y z
3 x y z
4 x y z
5 x y z
6 x y z
7 x y z
8 x y z
9 x y z
10 x y z
11 x y z
12 x y z
What I am looking for is that I want to repeat the rows after every third row in another column. For eg
A B C D E F G H I
1 x y z x y z x y z
2 x y z x y z x y z
3 x y z x y z x y z
View 1 Replies
View Related
Aug 27, 2010
I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:
Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod
Tod
Bill
Bill
Bill
Sue
Sue
Sue
Sue
Sue
View 6 Replies
View Related
Jun 30, 2012
I know this is probably very simple and basic but I have a workbook with a total of 8 worksheets.
The first sheet is going to be a summary of information contained on the 7 other worksheets.
All 7 sheets have the same header row and I would like to have it so that all 7 rows repeat the same headings and if I add more sheets they automatically also have the heading row appear.
Is this possible other than doing a copy paste when you insert a new sheet? Also is it possible to code it so that should a new colum (heading) be added to the first worksheet it would automatically be added to all the other sheets?
The first summary sheet will have headings that reflect the name of the employee and then the names of the following worksheets as the header row and will gather information about the progress of each employee on fulfilling the requirements setup on the other worksheets.... that is yet another question on how to get it to do this.... which I will post later..
View 4 Replies
View Related
Jun 6, 2006
I'm on a system using Windows XP with several user accounts. It's possible to share a document by putting it in a directory called, unsurprisingly, "Shared files' or somesuch. Anyway, I wish to put one of my Excel spreadsheets into this directory so that other users can see it. The workbook itself has about 10 worksheets and I want other people to be able to edit only one of them. So I used the "Protect Worksheet" option within Excel to protect those worksheets I don't want edited. However, I notice that it's still possible to delete a worksheet even though its protected. Is there a way to prevent from deleting worksheets?
View 2 Replies
View Related
Jan 5, 2007
i have a workbook with 6 worksheets in it. the user can put data into any cell in any worksheet. what i want to be able to do is prevent the user from accidently deleting any of the worksheets
View 8 Replies
View Related
Jan 19, 2008
In my program I have prevented the user from saving the Workbook with the following code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim p As String
Cancel = True
Do While Cancel = True
p = InputBox("Enter password to save file:", "Password Required To Save", "")
If p = "Enter Password Here" Then
Cancel = False
Else
yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password")
If yn = vbNo Then
Exit Sub
End If
End If
Loop
End Sub
I also need to prevent the user, when they use the save as, from saving certain worksheets. Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three. Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.
View 2 Replies
View Related
Aug 12, 2008
I am needing to disable 5 worksheets from being calculated under the 'AutoCalculate' default option of excel. Currently, I set all 5 sheets' .enablecalculation property to false (see code example below), then save the workbook. This should prevent these 5 sheets from being calculated when the workbook is opened again - however these sheets are still being calculated with excels default 'Auto Calculate' setting once the workbook is reopened.
My issue:
While having 'Auto Calculate' enabled, how is a sheet disabled from being calculated upon opening?
Current method of disabling worksheet - does not work tho.
worksheets("Mysheet1").EnableCalculation = False
View 9 Replies
View Related
Aug 28, 2007
I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that.
Sub search_box()
Range("B49000").Select
Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub
View 8 Replies
View Related
Dec 4, 2013
I've been looking for a solution to highlight all duplicates within a certain column across all worksheets in the workbook as the entry is made, no button to search for them. I have found bits and pieces, but can't seem to stitch them together to create what I am looking for, still very green with vba.
My workbook is a loading schedule at a warehouse, so there is a tab for each day (the date being the tab name, ex. '12.04.13'.) Tabs are continually added and removed to progress the calendar, and minimize file size. The column I am searching for duplicates in, is column L (or 12, however you wish to identify it.) If a duplicate value is entered, I want at the very least to highlight the value just entered, and the value elsewhere on the workbook, and if possible, have a msg box pop up stating the location of the other duplicate (or at least the tab (date) the duplicate is on.) The message box is more so for an in your face error check, with a built in GPS. Would it also be possible to only search from row 2 to 100, and ignore any further rows on each sheet?
The point of this, is to locate duplicates to make sure an appt has not been double booked, and both entries can be located to verify which entry has the correct information (carrier, delivery appt, etc.)
View 9 Replies
View Related
Dec 10, 2013
I'm putting a presentation together for work regarding false alarms and I'd like to count how many alarms have gone off at certain locations and I'm wondering if there is a formula that will accomplish this. I've attached a pic of a spreadsheet containing the data to be used. On the left side, there is a table labeled "Alarms" with columns displaying the dates, street numbers and street names. On the right side of the spreadsheet, there is a table labeled "Activations", which will be used to display the amount of single, double, triple, etc. activations.
For example, using the data, the alarm at "2 Red Oak Row" has gone off 3 times and since there are no other addresses where this has happened, a "1" would be displayed under the box labeled "3" in "Activations". Since the alarm at "4 Winding Way" and the one at "23 Blackberry Lane" have both gone off twice, a "2" would be displayed under the box labeled "2" in "Activations". The same would hold for addresses where the alarm has gone off 3 times, 4 times, 5 times, so on and so forth. In addition, if an alarm has gone off 10 or more times at any residence, the first 10 activations and any subsequent activations would be counted as a "1".
For example, if there is one case where an alarm at a certain residence has gone off 10 times (or more), a "1" would be displayed under box 10. If another residence's alarm also went off 10 times (or more), a "2" would be displayed under box "10".
View 5 Replies
View Related
Aug 10, 2013
I want to Autofill a Column C to repeat a set of data in Column A, if Column B contains an integer, as shown below. Being able to Autofill this data allows me to have a dynamic table for use on multiple projects that may have a different amount of rows in Column B.
Outflow % Table
25
50
[Code]......
View 6 Replies
View Related
Jul 14, 2006
what I need to do is , while an id value is constant copy 2 adjacent cells to a new location on the same row as the first occourance of the id value, then if the id is the same on the next row repeat the copy and add these 2 values to the cells next to the ones previously copied.
Once the Id value changes then the copy restarts on a new row which is the same as the first occourance as the new id value and repeats the same sequence as described in the first statement.
I have attached a sample spreadsheet showing an example of the source and how I would like it to end up, the colour is only there to seperate the outcome from the source and is not required in the code.
View 9 Replies
View Related
Jul 7, 2009
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
View 6 Replies
View Related
Jun 9, 2014
I'm trying to create a formula (if possible) that will repeat column headers down a row and repeat every nth time. I have text values in cells E6:AL6 and would like to use a formula that will make it so that E6 will show up B2, F6 will show up in B3, G6 will show up in B4, etc. And then when it gets to B35 (AL6), B36 will then revert back to E6, and the loop will continue - B37 will be F6, B38 will be G6, etc.
My thought is to create an offset/address of some sort that will read as OFFSET(E6,0,x+1) and if x>35, start back at 0. However, I don't think you can create such a formula using an offset?
View 5 Replies
View Related
Mar 23, 2014
I have values
2
8
9
As so on in my cells a1,a2,a3 as so on
And I want value of column a repeating five times of each cell value in column b
like result
2
2
2
2
2
8
8
8
8
8
9
9
9
9
9
and so on
View 3 Replies
View Related
Jan 15, 2010
Sub For_Adam()
For x = 1 To Range("G7")
Calculate
Range("G11").Select
Selection.Copy
Range("B15").Select
ActiveCell.Offset(x, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next x
Application.CutCopyMode = False
End Sub
Now all I want is for this to be repeated in the next column, so everything is exactly the same but it doesn't start in B15, but starts in C15, then D15 etc.
View 9 Replies
View Related
Aug 23, 2007
I have a rather large workbook with a lot of VBA behind it (about 1MB with no data, just formatting and VBA code). It all seems to work perfectly, except one sheet. It is a list of about 35 options with checkboxes beside each option. When the sheet is made visible through VBA code, the user has problems when scrolling the sheet.
It's hard to describe, but it all looks perfect until either the scroll-wheel or the scroll-bar is used to move around the sheet. The display then goes completely screwy. The heading row (which is actually a frozen pane anyway) ends up repeated all down the sheet, as do the column headers (A|B|C|D|E...). If i select another sheet and then back to this one, it's fine, it appears to be only when it is un-hidden through VBA.
I have attached two screenshots as an example... the only thing I have done between the un-scrolled one and the scrolled one is scroll the mouse wheel down and back up once.
View 9 Replies
View Related
Jun 11, 2008
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
View 3 Replies
View Related
Jan 5, 2014
I have 2 column in Excel sheet A and B .
What i need that when i enter values in the first column A the column B copy this values , but when i repeat value in first column A .
The column B would not copy it that all I need .
View 2 Replies
View Related
Jan 23, 2013
Here is an example list:
Networks
Hardware
Networks
Software
Networks
Resources
Apps
Hardware
Apps
Software
Apps
Resources
Domain
Hardware
Domain
Software
Domain
Resources
Print
Hardware
Print
Print
All I'm trying to do in the second column is something that can say if there is a change in column A, then restart the order, or not..
View 9 Replies
View Related
Oct 28, 2008
I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
View 4 Replies
View Related
Apr 14, 2014
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies
View Related
Jul 13, 2012
I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB:
'this line will be modified so that i cycle through the non blank cells that i have found
For row_cycle = 1 To 7
'not signifcant
If Abs(ActiveCell) < 1.96 Then
Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
View 3 Replies
View Related
Mar 25, 2014
1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.
I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.
View 4 Replies
View Related
Jan 18, 2008
I Spoke too soon here, I am getting value errors, how to do it with conditional formating. I tried if cell value is =to #DIV/0! then white (dosent work). Richard this dosent seem to work mate =IF(SUM(F6:V6),"",AL6/SUM(F6:V6))
View 9 Replies
View Related
Feb 15, 2010
I have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
Next
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub
The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???
View 2 Replies
View Related
Jan 9, 2008
I have a dynamic chart that allows the user to select the starting and ending dates for the data range by using a scroll bar.
The dates in the data table are input daily and are generally in sequential order, however there are gaps between dates (eg. 1/4/08, 1/7/08), such as would occur over a weekend or holiday period. The dates are used as labels for the X-axis.
The problem is the chart interpolates the dates in the "gap" and includes them on the X-axis and plots them as zero values.
I would like for the chart to display only the dates and values actually in the dataset and not interpolate and include dates/assign zero values.
Haven't had any success in searching the forum. Can this interpolation be prevented?
View 5 Replies
View Related
Jan 16, 2009
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies
View Related
