Minimizing Data Entry With Formulas
Aug 23, 2007
I have a few columns which a user needs to fill out manually, this is what i am trying to minimise and a few 'what ifs' spring to mind so here goes.
Firstly, a formula to do the following:
Column E1 is always a date 30 days from the data entry date, so:
If A1 has a value (Always name surname 'John Doe') AND B1 has an email address (John.Doe@somewhere.co.uk) show todays date plus 30 days otherwise stay empty.
My second question might be more complex:
If B1 is email address i wondered if A1 could snatch the name with a formula:
B1: john.doe@somewhere.couk
A1 = john doe
However the problem is lets say theres more than 1 john doe, the email address might be john.2.doe@somewhere.co.uk but i still need A1 to read john doe as i have a macro set up to send an email to the value of A1:
Dear + A1
View 9 Replies
ADVERTISEMENT
May 21, 2012
I am trying to minimise the space I use to visualise my data.
I have three data points:
£26 - (Min)
£45 - (The data point we are interested in)
£85 - (Max)
I want to represent this data in the following format:
How can I get Excel to do this?
View 2 Replies
View Related
Oct 6, 2008
Using Excel 2003. I have a macro set up to copy all information from one Excel workbook to another. There is quite a bit of going back and forth between the 2 workbooks. I ended the macro by clicking in a particular cell in the origination workbook.
When I exceute the macro though it minimizes both workbooks. I have used the VB Editor to make sure that there is code such as.
View 6 Replies
View Related
Feb 21, 2010
I am running a few loops within one big loop,
1st loop gets the moving average of the first 10 rows.
2nd loop uses that moving average to calculate the exponential moving average of 5,000 rows.
Now, after the first loop is run, I put that value in a Cell in Column (O). The 2nd loops works off of that cell to calculate the next exponential moving average and then that to calculate the next, and so on and on.
then I run two of the same loops as above, but with different period (for the moving average) and everything is printed in the next column, P.
THEN, another loop subtracts the column O from column P, and prints the value in the next column, Q.
Now, this takes up 3 columns, and I only need the third value, the one printed in column Q. Is there a way where my loops can store all of these values in some type of an array (instead of filling up a column on a worksheet) and then for me to simply just subtract the two arrays and have only that show up on my worksheet?
View 14 Replies
View Related
Nov 23, 2012
I have prepared an excel workbook with macros for a bill. In that I have protected the workbook (selected window also) after reduced the menu bar to just ribbon (HOME, INSERT,PAGELAYOUT,.......), so that I would get enough space. I have send it to my friends as WinRaR file. But when they open in their computer the sheet is not getting enough space to display the entire page as normally no body used to minimize the ribbon and formula bar. This is making lot of inconvenience. I tried one macro but it not recording the actions on ribbon.
View 4 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
May 12, 2013
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies
View Related
May 5, 2009
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies
View Related
Jul 15, 2009
I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
View 14 Replies
View Related
Sep 11, 2013
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.
View 3 Replies
View Related
Apr 23, 2013
is there a way to lock certain cells to allow data entry but not allow data to be removed.
View 2 Replies
View Related
Mar 14, 2014
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies
View Related
May 1, 2009
I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies
View Related
Jan 6, 2009
I have a spreadsheet which when I enter data into cell E15 (this cell is dropdown list (AL,ML,SDY,Toil)) it will copy data from another part of the spreacheet and past as value only.
So the aim is to select e15 pick from the dropdown this activates my VBA that copies data from cell A" and pastes special in A3 will this work on a dropdown and if so what code do i use. If it wont work what can i do instead.
Example I have used;
Sub ch()
If Range("F15") = "AL" Then
Range("A2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Case Else
Exit Sub
End Select
End Sub
View 9 Replies
View Related
Aug 21, 2007
I've been searching but I can't find an answer that fits. Here's the closest I've come. Force Date Entry on Particular Condition. What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data. Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.
View 3 Replies
View Related
Oct 19, 2006
I have a programme that will be accepting numeric data through a DDE link (Winwedge). My question is; is it possible through VBA to ensure data can ONLY be entered into a specified range from the DDE link?
View 2 Replies
View Related
Nov 28, 2013
I am trying to find away to split up data that is entered as text into a cell,and split that text data into 3 columns. Sample text enters in a cell can be:
10-3 or 1-3-1 or 12-11-0 or 1-6 or 8-8.
As you can see, the text entry can have two or three numbers in it. I would like to split this data so it looks like this: 10-3 would be cell A5=10, cell A6=3 and cell A7 is blank.
Another example would be 1-3-1 would be split A5=1, A6=3 and A7=1.
I know about the text to column function, but that is not the case as I want the data to split automatically with a formula.
View 3 Replies
View Related
Sep 29, 2008
However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.
View 3 Replies
View Related
Feb 12, 2009
I've created a userform that will pop up automatically when opening my workbook. My workbook will have many worksheets identified by the employee's last name, then first name (i.e. Doe, John) Etc. I ideally would like the name entry in my user form to locate the correct worksheet based on the sheet name and then all of the other data in the user form be put in the next available/appropriate row and column, etc. of that particular sheet. This user form would save me a ton of time keeping my training logs up to date, instead of having to locate each employee tab manually and then enter the data and then look for the next tab, etc.
At the end of the year I'll manually go to each worksheet and balance that year's training time and cost (as you'll see from the attached example), but I'm hoping that the user form entry will identify that "yearly balance row" as having data and would skip to the next row to begin the next year's training entry. The "Enter Data" Button would place all of the user form data (other than the name) into the appropriate row/column and I believe my refresh button will empty the user form fields, but I'm hoping it won't delete the data that was just previously entered into the worksheet. (See attachment)
View 4 Replies
View Related
Sep 20, 2009
I'm trying to make a macro that will help with data-entry. In two columns of each sheet (columns D and J), I'm entering school grade information ("K", "Pre-K", "1", etc, up to "12"). As I move off of each cell with data so-entered, I want to have the entry formated such that "1", becomes "1st", "12" becomes "12th", etc. I've put code into "Worksheet_SelectionChange" and the code works but...
1. The cell value is not changed upon leaving the cell, only after re-entering the cell. So, I type "1" in the cell, move on (the cell value remaining as "1"), then go back to that cell and the value becomes "1st" like I want. So each cell requires entry and then (for example), left arrow, then right arrow, then move on the next cell for data-entry. Since I'm only saving a few keystrokes for each cell, this approach is just about a wash. I need to have the data changed upon leaving the cell. Is there a way?
2. There are 50 or 60 sheets in the workBook and I have to copy that code onto each sheet. Is there a way to move it to the workBook level? Failing that, is there a way to ease the process of pasting that code to each of the sheets?
View 3 Replies
View Related
Feb 13, 2010
see in atteched file User-form "frmEntry" work fine to update the data in Trader worksheet. but i need help to program.for the "frmExit" which i program. u can look in to the database "Trader Worksheet" .it enter the data in next available row.instead of up dating the corresponding existing stock ticker.to sell the stock.also when i enter the second sell of stock it change the row which is already their.not in new row.
View 4 Replies
View Related
Apr 18, 2006
Is it possible to allow data entry within a listbox?
View 9 Replies
View Related
Aug 24, 2006
I need to have a message box appear if a user tries to enter a value not between 992200000 and 992299999 in the range B11:B1182. I used the following code to test this out (only for B11).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B11").Value < 0 Then
If Range("B11").Value < 992200000 Or Range("B11").Value > 992299999 Then
MsgBox "Please enter the Associate ID"
End If
End If
End Sub
The problem is A: I need to apply this to every cell in the aforementioned range. And B: I need to only restrict this when there is a number in the cell (I tried to add the first If statement to do that, but it didn't work).
View 7 Replies
View Related
Sep 27, 2006
I have managed to put together a userform to input the data from a safety audit card to populate a spreadsheet so that i can generate stats etc (every ticked check box inputed a tick onto the spread sheet into the correct colum for the data, on resetting the form it went to the next line as per the userform database example available here). This was working fine as it only had 5 check boxes and although my code was probably a bit rough, it did the job. The code I was using is:...
View 9 Replies
View Related
Mar 10, 2007
I have the following code in a userform, assigned to a command button.
Private Sub CommandButton1_Click()
Select Case TextBox2.Text
Case Is = ""
MsgBox "Please enter the Name of recipient."
Case Else
End Select
Select Case TextBox3.Text
Case Is = ""
MsgBox "Please enter the E-Mail Address of recipient."
Case Else
I modified this code as it was starting at the bottom of the worksheet & checking for the 1st vacant row from the top. I wanted it to start at the top & look for the first vacant row down. That way, if there was a entry that was deleted, it would find that row 1st & keep the list in order. This code worked fine for the last 2 days, but, this morning, nothing is being entered on sheet2.range A2 or lower.
View 3 Replies
View Related
Jun 4, 2007
Need to create a Data Entry Form in excel?. I would like to insert data to an excel database using a form.
View 4 Replies
View Related
Aug 13, 2014
I am trying to arrange a dependent drop-down list and have the basic listing working. My problem however is that when i pull data from sheet "Mon" for column C using indirect it works but when I try to do the same thing for column D i get the same data as column C. How do i get indirect (or offset if it works better) to allow me to reference the alternate data 1 column over?
View 3 Replies
View Related
Feb 6, 2009
I know this problem could be easily solved with the use of access. Unfortunatly I can only use excel. I am creating a uniform stock database. I a trying to create a user friendly face sheet, so that the operator does not have to have any knowledge of excel to use it. My question is to do with a data entry form. Sheet 2 of my spreadsheet has a list of all uniform in stock. At the moment it has two coloumns, "uniform type" and "uniform size". Is it possible to create a form on sheet 1 (the user interface) where a user could imput the type and size of an article of uniform that had just come in, and have it automatically added ot sheet 2?
View 4 Replies
View Related
Feb 26, 2009
I want to limit data entry in a range of cells to Y or N (Upper Case)
I have tried data validation, list, but this still allows y or n, I want just Y or N
View 3 Replies
View Related
