In cells B4-K28, I would like the cell fill color to turn red if there are more than one entry of the smae persons name.
Example: If I type David in B4 and David in K28, I want both cells to fill in red. Any duplication of names needs to turn red in both cells.
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
First post here and I hope to find a solution
I have 2 columns with data. The first one has 16000 data (6 digit numbers), which are 2 or 3 or 4 times the same (duplication). The second column has a list with name folders.
Question: How can I delete the duplicate/triplicate entries, and leave only the one I want, according to a priority list I have of name folders?
i am trying to make a double entry bookeeping system in excel just like VT cashbook and VT transaction
View 3 Replies View RelatedIn Column B I have dates and in column C I have values correponding to the dates in B.
In Column J I have dates and in column L I have values correponding to the dates in J.
The first date entry in column B and J are equal but after that it is different based on a workday formula adding months, years, days etc. etc.
B31 = 06-apr-09 and J31 = 06-apr-09
B32 = 06-apr-10 and J32 = 06-Oct-09
B33 = 06-apr-11 and J33 = 06-apr-10
B34 = 10-apr-12 and J34 = 06-Oct-10
B35 = 08-apr-13 and J35 = 06-apr-11
B36 = 07-apr-14 and J36 = 06-Oct-11
J37 = 10-apr-12
J38 = 08-Oct-12
J39 = 08-apr-13
J40 = 07-Oct-13
J41 = 07-apr-14
As may notice column B adds 1 year to the previous date while column J adds 6 months to the previous date. (This is changing and B can add 1 month while J adds 3 months etc. etc.
I need a formula in column N that will look at the dates in column B and in column J and list them in column N in ascending order while not taking into account double entries.
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).
The problem is that if there is no data in a participants column then excel correctly gives you readout of “#DIV//0!”. On my results page this #DIV//0!” makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0!” to “0” or even turn it to a blank cell?
I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.
View 3 Replies View RelatedIs there a way for me to enter numbers in my cells and have them a specific color? I want all my odd numbers to be bold red with a yellow highlighted cell, my even Numbers to be green with a gray color cell.
I want it to look like this 7 and 8. I can not seem to find anything to show the high light but basically the red 7 will be in a yellow box and the green in a light gray. I will be entering rows and rows of anywhere from 4 to 8 digits
I have attached a small file with a very simple conditional formatting formula used to turn a block of cells green.
However, the formula does not seem to work properly as it only turns half of the cells green. Columns B1 to B18 are also supposed to turn green, but instead remain clear.
I need to know when something will expire ahead of time. I have certain dates located in cells that an item will expire, for example, a product life of "10-Jul-06 - 11-Aug-09"
What I am trying to do is have an automatic e-Mail sent out "3 months ahead" of the time something will expire. So in this case, for the above example in August of 2009, I need an email trigger sent out in "May" stating that the item is about to expire. Sort of like an "alert".
I found some code that will allow me to run a macro which triggers an email. It works, but I need to have it automatically do it when I hit a certain date..not do it "manually" like when you run a macro back on the excel sheet.
Ultimately, I need an email sent out as soon as a certain day/month comes that will trigger an email message through Microsoft Outlook.
My Code so far (in VBA module):
I am looking to double click a cell in Column "A" to copy the ajacent cells to another sheet.
In worksheet "Summary". When I double click Cell A5, I want to copy Cells B5:D5 to Sheet "Timesheet", insert Row "5" and paste in Cells B5:D5.
I have a master spreadsheet being updated by several people, how can I/we, get a notification set up for when ever someone updates the master spreadsheet.....
View 1 Replies View RelatedI have a row of values TRUE and FALSE from a IF statement..
If one or more are true can i get a notification to come up saying
"Rents are Due" Press ok to continue..
How to create a macro which would search through certain columns and post an alert/notification with the employee name (at the top of the column) and date (to the left of the column) if the cell value is less than 12.
View 2 Replies View RelatedI have several linked files to a master excel document that uses filters.
Unfortunately, if I dont remember everytime to turn off the filter before I exit the file, the other files read data incorrectly.
I could set up an alert/notification that tells me these filters are still on before I exit the file?
I'm trying to validate 2 cells and if both matches it should return a value from column 3
I've gotten so far as it would return a error when criterea is not met.
However is it finds a match it always returns the first value.
My current formula is: =INDEX($C$24:$C$29,(AND(MATCH(A12,$A$24:$A$29,1),MATCH(B12,$B$24:$B$29,0))))
I can't find a way to make Excel validate the first 2 columns and return the value from column 3.
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
I have two columns in a spreadsheet.
In cell "$D$3" is the word "Period 1".
I want to be able to do the following.
Double click on cell D3, where the word period 1 is.
VBA then looks up that period in column B, and finds the corresponding weeks for period 1 from column A.
I then want VBA to list these weeks next to the period 1 I double clicked, so in column E.
I have attached a spreadsheet as an example.
we have a date in a cell let's say : 20/07/2014 (present day 16/05/14)
I need to add a conditional formating rule so when the present/real date will reach 15days before the 20/07/14 and until the speaking date the cell will be colored as notification to upcoming deadline (20/07/14)....
Meaning that the cell will be colored on 05/07/14 and 06/07/14....etc. until 20/07/14.
How to send an email notification. Saying something like "New Entry Added" from workbook to my outlook express email account.
Users have one sheet with the userforms on it. They submit entered data and the data workbook is opened in the background and the data saved, and then it's closed again.
I would like the email notifications, as there could be up to 100 people adding entries at various times when something has been added to the data workbook.
My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually )
The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)
The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.
Capture.PNG
What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.
I am trying to set up a double click macro that checks the color index of the exact same cell you double click on, but in another sheet. What would be the syntax for that?
View 2 Replies View RelatedI'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on.
The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc.
Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
I have an interesting question for everybody here. Let me see how to best explain it.
I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.
'Back fill last modified
'I need to start with the first data row (3) and the action plan completion column
Let intcurrentrow = 3
Let intcurrentcol = intactionplancompletion
Do Until intcurrentrow > intmaxrow
If Cells(intcurrentrow, intcurrentcol).Value = "" Then
Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value
'Need to ensure that everything is formatted as a date
Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM"
End If
Let intcurrentrow = intcurrentrow + 1
Loop
Later, I place this formula in a given cell.
=If(I9="Open-Remediated",M9-K9, If(I9="Closed-Remediated and Validated",M9-K9,"" ))
The info I copied above is placed into the column K.
When I run the macro, the cell formula outputs "#value".
However, if I go double-click then enter the cell calculates just fine.
I've tried excels overall recalculate function, but that doesn't change this.
Does anybody know how to simulate a double-click on a cell?
I would like to have Excel automatically send a Console Message whenever a user enters a value greater than zero into a column. In the example attached, I would have received a message on 12/03/2009 and 12/07/2009 because the user entered a positive value into the "Fail" column. I know macros can be written to send E-Mail messages, but I do not want the user to have access to E-Mail or the Internet. Ideally, the message would be sent instantly, at the worst, the next time the user saves the file. *Edit-I really do not care what notification I get. It can be a message, an image on my screen, whatever just as long as I get notification that the user has entered a positive value into the "Fail" column.-End Edit*
View 10 Replies View RelatedI am wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
View 9 Replies View RelatedIs there any method to disable notification / error message of password protected sheet when we try to edit restricted cells?
Note: In my scenario I've to allow user to select protected cell; that's why cell selection redirection is not useful for my.
writing the correct command to auto popup a calendar when a user doubleclicks in any cell in a range of cells. I have the code for individual cells but would like to apply to a very large range of cells and do not know what the correct command is.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$C$160", "$C$161", "$C$162", "$C$163"
Cancel = True
Call OpenCalendar
End Select
End Sub