but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
I am trying to limit the magitude of number entered into a single cell. For instance I want the highest number that can be entered into a cell to be 1500. How can I do this?
Basicaly what I am trying to accomplish is the use of a single cell to enter the values 1 thru 60, so starting at e2 for instance, if I enter the number 1 then cell f2 will populate with a 1, if I were to enter the number 18 then the cell w2 would populate with an 18. So the range of cells that need to populate in this example would be (f2:BM2) for row 2. Furthermore if I were to enter a -1 or a -18 in cell e2 then those numbers would be eliminated from the appropriate cells on row 2. The next 299 rows would need to perform the same way for a total of 300 user input rows.
The way that I'm imagining e2 would behave in the example would be...type a 1 then enter....18 then enter....31 then enter and so on. Columns f thru bm would locked and unselectable....so a tab should send me back to a3.
Reasoning.... this is a simple scoring system based on 60 codes and data integrity is very important and I want to eliminate as much human error as possible. I will be setting autofilters and such later on.
I have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
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.
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.
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?
How to set up a sheet with single-keystroke data entry. I am writing a sheet to store golf scores. I need to be able to key in scores at a high rate of speed.
The allowed data are: 1, 2, 3, 4,5 6, 7, 8, 9. I want the operator to be able to press 3 (or Alt-3) and get:a) a 3 is typed into the cell. b) the cursor moves to the right one cell. Etc. for 1 thru 9.
I would like to be able to enter a number in a single column, and then Excel adds this number to a total column each time it is updated.
This is for entering via a PDA, so I need a simple entry method, ie to enter a figure, and then keep a running total every time a new figure is entered. This would only use the one column and overwrite the number each time, rather than adding up individual columns.
The attachment shows the items listed in column A, column C3 - C10 would be where I would like the number of items entered, and as I go round, I would just update the last figure. I would like the figures from C3 - C10 to be added to the Total column D3 - D10 for each item.
I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
i have managed to pull together some code that will deny people adding data into cells if they have 5 of the same entry. the entries are entered in a range and are matched against a single cell outside of the range. heres the
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim greycell As Range, i As Long If Not Intersect(Target, Me.[grey]) Is Nothing Then Application.EnableEvents = False For Each greycell In Target If WorksheetFunction. CountIf(Me.[grey], greycell.Value) > 5 Then i = greycell.Interior.ColorIndex greycell.Interior.ColorIndex = 3 'red greycell.Select MsgBox "no cell entry past 5", vbCritical, "ERROR" greycell.ClearContents: greycell.Interior.ColorIndex = i End If Next Application.EnableEvents = True End If End Sub
what i need with is adapting this code to match two ranges as i cant use the worksheet_change event twice. i need it to be as if they were seperate events but are merged together. eg:.............
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
When users enter data, I want them to enter a percentage into column D OR a dollar amount into column E, or enter nothing at all, but NEVER to enter into both D and E on the same row. They are set to zero by default.
It would be great if a message box could just pop up saying they can do one or the other, and if they've already entered into the other cell they need to zero it out before changing this cell.
I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.
So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
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
A B Bob Fenton 05/04/08 Rob Smith 05/06/08 Al Feth 05/08/08 Al Feth 05/18/08 Al Thomas 04/23/08 Rob Smith 05/23/08 Bob Smith 04/22/08 Bob Fenton 05/15/08 Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
I am using a spreadsheet written by someone else that contains a type of cell entry I am not familiar with. These occur in several tables. The first 3 columns of each table contain numbers or basic formulas. These are followed by two columns in which every entry appears to be the following:
{=TABLE(,B33)}
Despite the same apparent "formula", the number displayed in each of these cells is different. If I click on the formula display box (to the right of the cell address box) to edit the "formula", the brackets disappear.
Can anyone tell me what sort of beast I am dealing with here?
I want to require cell entry by users. For example, if a user wants to entry information in cell C1, they must first enter information in cell A1 and cell A2. If there is no information in both of those cells, I want to display an error message to the user indicating they need to first enter information in those two cells.
I have a cell in which I want either a Y or a N entering. I do not want this cell to be left blank.
I can add a validation so that nothing but Y or N can be entered but it doesn't stop it being left blank. I entered an N (as a default) forcing the user to change it to a Y if needed but I don't want them to be able to delete and leave the cell blank.
Is it possible to count the entries into a cell? Example cell A1 (=5+5+5) this would total 15 but could it count that there were 3 entries? Is it possible to do it both postive and negative. Cell A1 (5+5-1) this would total 9 but it would count 2 if you want negative entries subtracted. or it could count 3 if you wanted all entries. I think this would be two different formulas.
I have an Excel sheet which serves as an input form. I would like to set something up where if the user inputs any value into the cell range B12:B100 then they are forced to also enter a value in column J of that same row.
Maybe something could be done where if they enter a value in B12, then they cannot enter a value in B13 before entering a value in cell J12?
What could be the formula if i want a particular cell to auto sum the data input. For example if i click cell a1 and entered a number and again entered a number , the previous entry will be added to recent entry.
What do I need to add to this script in order to stop the macro after the last cell with data. Currently it will continue to add and additional sheet and then error out b/c the next cell is blank and it doesn't know what to enter as the worksheet's name.
For Each c In Sheets("Sheet1").Range("A1:A24") Sheets.Add ActiveSheet.Name = Right(c.Value, 30) Next c
I am using the code below to prompt the user to enter his/her name in cell B4. What I would like to do is have it so that after the user enters his/her name, that cell is locked and cannot be cleared.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$4" Then Range("B4").Value = InputBox("Please Enter " + Range("a4"), "Company") End If End Sub