Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
My colleague in accounts has a sheet that lists all the invoices and values and other 'accounts department things' that I know little about.
Anyway, she has to manually input all the invoices she receives onto this excel document but sometime receives duplicate invoices (& inputs them twice be accident).
Is it possible for someone with macro skills to create a macro to run in a worksheet so that if she enters a number in a given column more than once an alert box appears to tell her. I've summarised the 'rules' below*
*Can is be written so that I can edit which column the alert is based on?
[In (for example) column F is a list of invoice numbers - all unique]
If user types a number in column F that matches a number that already exists, show alert box "THERE APPEARS TO BE A DUPLICATE ENTRY IN COLUMN F"
====
Any further clarification, let me know i'll try to post a blank sheet if my boss lets me.
Is there any way to get Excel to disallow you to enter in unique values into the same column? Like I know you can search for and delete duplicates, but I am wanting to get excel to disallow duplicates from being entered and/or give me an alert that what I just entered is a duplicate.
View 4 Replies View RelatedI just discovered VBA coding today and was wondering if I could use it as an audio alert. I found a couple examples online but don’t understand the coding language of excel. I have a feed link from my trading platform to excel that feeds in live data. What I would like to do is have an alert for when Range ("C4").select is >=1000 Alert Ring.wav and when Range ("C4").select is <=-1000 Alert Chimes.wav.
View 5 Replies View RelatedEach month I get a report that I process using a macro. The problem is that each month the name of the file changes and is different. When I run the macro and it crashes I have to de-bug by going through the code to change all references to the file name from the the previos run and change to the latest file name and then re-run the macro. There must be a better way.
I would like to learn the code that sees the open book and then refers to it for the run.
What is the best way to do this?
Is there a way to have Excel alert me (via cell color change preferably, but another method would be fine also) upon entering a number that has already been entered on that worksheet?
The problem I have:
I have 10 columns in a worksheet that I keep track of serial numbers in for work... Sometimes someone enters the wrong serial number or doesn't update a serial number so we will have the same serial number in two different places and another agency will call us to correct our mistake, making us look inept.
I figure the best way to alert people of this input error would be to change the fill color of both, or the active, cell(s) so they could immediately begin looking into the problem.
For Example...
In Excel:
Question Answer
What is his Name? John
How many apples did he buy? 8
How much did the apples cost? 50
In Word: John is in cell b2, 8 is in b3, 50 is in cell b4
John bought 8 apples totaling $ 50 dollars.
Based on the information typed in the answer column (excel spreadsheet) I would want a word document to automatically generate a sentence.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
In sheet1, (B2 & C2) are the numbers entered by the user. After they entered, it will create rows of table according to the number of groups they entered on the respective sheets.
how do I let it create the groups itself?
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedI would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.
The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.
Im trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
View 9 Replies View RelatedI have on the first tab a screen where you can fill in the name of the .doc or .pdf file.
That works fine, but now I would like that when I fill in a number and click on test, it opens te .doc and .pdf file that is attached for that number.
If you take a look at the pdf file you will see a second and a third tab.
First column is the number they need to fill in and the second column is the name of the .doc or .pdf that needs to be opened with that specific number.
How to link it and open both files for that specific number.
But not every number has a .doc as well as a .pdf.
if I use the following formulae in A1 and fill it down, I get the serialization 1,2,3....etc. =(INT((ROW()-1)/1)+1)*1
But if I begin formulae at A11 and fill down, I get 11,12,13.....etc. Now I type 1 in A11, how do I modify the formula so that A12 will be 2, A13 will be 3, A14 will be 4 and so on ?
I am entering values in columns AV and BD of the attached spreadsheet. Rather than manually editing each value to adopt the decimal place value of the adjacent cell is there a formula that can achieve this before the value is entered? The decimal place values are determined using a VLOOKUP table (column3) on the NES tab.
View 2 Replies View RelatedI want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies View RelatedI have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
I am trying to simply count the number of times each entered name appears on my list IE if John Smith appears 3 times in one sheet, in a column after his name would simply be the number 3. I tried this doing =COUNTIF(A8,A:A) Where A8 is his name and column A is all names. I keep a return value of 0 every time!!!!! I even tried =COUNTIF(A7,A12) where they were both the same names. And yes,I did do Ctrl + Shift - enter
View 2 Replies View Relatedtrying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
Example;
Cell 1 equals a number
Cell 2 is the number entered into cell one times(X) 35 up until the number 6 is entered. Any number above 6 is Times 50. So. 1-6 is times(X) 35 while 6+ is Times(X) 50.
I am. Here's another example:
A salesperson gets a commission of $35 for each widget sold up to the first 6 widgets. If he sells more than six widgets, he gets paid $35 for the first 6 and $50 for anthing over 6.
The sales person will enter the number of widgets sold into a cell and the formula should do the rest.
I have been stuck on this for months.
I've done lots of looking on the forum, and the helpfiles, but just cant seem to format my text box properly! Its a form where the user inputs to the text box. I want to control thatway where they enter numbers e.g 05052008 and it gets converted to 05/05/2008 or 05-05-2008. I've tried a range of things, but am really stumped!
View 2 Replies View RelatedI created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I have Two Sheets in same workbokk.
Sheet1 Sheet2
Sheet1 B2 = "any numeric value (whole digit i.e 10 or 55 like) - user input "
Sheet2 Starting point is cell B3.
What need to happen - As soon as user enter number in Sheet1 B2 = any value say, 20 then,
Sheet2.Select
Range("B3").select
suppose i assign value p as,
dim p
sheets("sheet1").select
p = range("B3).value
now here i want to indetify this number, i.e.- 20 (User input) and then as soon as user enter this value,
step 1 - sheet2 get select.
step 2 - from B3 sheet2, by row wise it select next 20 row as well next 20 columns.
for eg.- from B3, the data should select like following,
B3 to U22.
and this selected area highlight with single border only. That's it.Therefore, my main problem is how excel will understand this numeric number ?
I am trying to use VLOOKUP to auto-populate a description from an entered part number. After checking up on how to do this in several different places I applied this formula to the relevant cell but all that it returns is #N/A.
I am very confused as all seems to be correct, but I am new to this and I am sure I am missing something silly. :P
On entering a part number into cell C13 on sheet 'Stores Receipt' it should search and find that number in column A on sheet 'Product List', it should then return the adjacent description from column B on sheet 'Product List' and show this in cell C17 on sheet 'Stores Receipt'.... Sounds simple hey! :D
The formula used is:
Format Random Number Cells As Draw Result Entered ...
View 9 Replies View RelatedI used the following guide to construct a hyperlink formula which takes me to the coresponding part number entered into a box,
http://www.ozgrid.com/Excel/hyperlink-lookup.htm
It works well in the database I set up with all the part number the problem comes when I tried to use the same hyperlink formula in a diffrent work book referencing the database.
I get a correct returned line reffrence from the macro but it will not open a link to the work book
=HYPERLINK('Database Final.xls'!wbsheet & ADDRESS(MATCH(N16,'[Database Final.xls]DATABASE'!$A$1:$A$9999,0),1),N16 & "s' Info")
I need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.
View 6 Replies View RelatedI have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I am creating a review sheet/skill matrix using excel and need to be able to convert a particular selection to a number in the adjacent cell to work out the users efficiency (long story that i wont bore you with)
So basically i have a question/statement in cell A and a drop down answer menu in cell B containing up to 5 possible answers (very poor, poor, ok, good, very good). In cell C i would like to insert a formula that allocates a number dependant on the selection in cell B. So if the user selects very poor in cell B cell C states 1, if cell B has poor selected then cell C states 2 etc etc