Re-entering Cell Value
Dec 7, 2009
In some of my formulas, I need to re-enter the contents of the cell in order for a formula to compute.
For example, I am doing some math on a cell whose original contents are "10:21:00 AM" The formula returns a #NUM!. If I retype 10:21 in the cell and hit enter, the formula will now calculate properly. I have reformatted the entire column several times so that it is a custom hh:mm format. The original content also shows as time so I don't think that it is a formatting issue.
My worksheet has about 20,000 rows and this only happens for about 100 of the rows. It is pretty time consuming to manually go fix each one. Any ideas what is going on here?
View 6 Replies
ADVERTISEMENT
Jan 5, 2007
Earlier this morning I had a telephone call from a customer who uses a spreadsheet I created.
She explained that once she'd entered information into a cell [and clicked elsewhere] she couldn't then add to the former; rather, she had to click on the cell and type it all out again.
I assumed that, as an inexperienced excel user, she was unware of either double-clicking the cell, or selecting and pressing F2. I explained these methods to her.
She later emailed me with this:
"Further to our phone conv this morning I have just tried double-clicking on cells but it deletes everything in the cell rather than lets you edit what is there – F2 does the same thing. This applies to any cell, whether it is text, date or number."
I've been using excel for a while now, and I've never come across such a thing before.
Has anyone else? Could it be to do with her own excel settings? (other customers have had no such problem)
View 9 Replies
View Related
Mar 16, 2007
What I'm trying to do is enter a value/text into a cell - say (A1). After I enter the value/text I would like to have a new tab populated with all the information on the current sheet and the tab name referencing cell A1. I tried running a few different scenarios using macros and have come up short. I want to be able to do this multiple times with different values/text. For example if I enter B606123 into cell A1 I want a tab to be created named B606123. Then later I might want to add B606096 and have that created without effecting B606123.
View 3 Replies
View Related
Apr 8, 2009
I want to convert a range of cells to "number" format from text, and then refer to them in a formula that's somewhere else. However, if I do the format from VBA, I can't get the change to be reflected without the following
View 4 Replies
View Related
May 29, 2009
In one column (Column A) I have a column of data which contains long descriptive text - i want to extract a specific piece of text from this descriptive text and enter it into the next Column (Column B).
E.g. Cell A1 = "Hello Buddy, how are you?" - i want to extract the word Buddy and enter it into cell B1
then A2 into B2, A3 into B3, etc, etc,...
Is there a simple macro out there that can do this?
View 7 Replies
View Related
Feb 6, 2014
i have 5 lists of names which i want the sheet to enter depending on the cell value of C1. C1 is a drop down of 5 different departments so when C1=department 1 for instance i want A4:A25 to automatically update the list of names that i have against that department.
im sure its fairly simply but i just cant get any formula i use to work!
View 6 Replies
View Related
Oct 29, 2013
I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:
C:docsfolderdataClass D4John WayneProfile.xlsx
Would enter "John Wayne" into cell. The cell is namely the "name" cell.
I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).
View 3 Replies
View Related
May 29, 2014
I have worksheet that contain values in column E and G and the total in column K. E and G are unlocked for manual input and column K contains the formula for the total and is locked. From time to time a certain row will not be applicable and before I started locking the cells in column K i would just put "not applicable". Now that it's lock I can't do that. My only option now is to put O in column E and G but I would rather put "not applicable" in column K. Is there a way around this. I was thinking of something along the line of a macro that would unlock the worksheet input the text and re lock it. The macro would have to lunch when a locked cell is selected or have a button that would launch the macro an would prompt for which cell the text would be entered. Is this possible.
View 7 Replies
View Related
Feb 2, 2014
0: i have a sheet 1 protected and few edit range (Col A,B,C,D,E and F).
1: I have a validation on multiple adjacent columns (like Col A, Col C, Col E).
2: I need a code where if i select value in col a (cell A2) cell B2 should get current date and Time and both the cells (A2 and B2) should be locked. when i select data in cell C2 , D2 should get current date and time(Cell C2 and D2 should be protected).
3: User can enter data in A3 and B3 will get current date and Time (now A3 and B3) should be locked.
4: I need a function where user can call it and unlock the locked cells and edit the data and re-lock the cells again.
apart from the Col A,B,C,D,E and F, other columns and cells remain locked.
View 2 Replies
View Related
Mar 4, 2014
I have a requirement to enter 25 formulas into 25 different cells. The formula is this:
[Code]......
The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.
I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?
View 5 Replies
View Related
Mar 1, 2012
Having the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.
Number
DateStart timeEnd TimeActual Time takenComments
123459132546132156135469654814846543
View 1 Replies
View Related
Feb 9, 2013
I have a macro that is woking that is activated either via Ctrl-p or by an icon in my quick links.
I would like to call the macro when I enter text in the input cell C7.
At the moment I enter text in C7 and then call the macro to perform a series of tasks.
I would like to make the macro and tasks perform when I enter the text in C7 and hit return or enter key.
Is this possible??????
View 3 Replies
View Related
May 9, 2014
I am trying to write a small bit of VBA code, so that I can record the row and column of the active cell. I have got it working when I CHANGE the value of the cell, but I need to get the code to fire off when I ENTER the cell.
The code section is below:-
Private Sub Worksheet_Change(ByVal target As Range)
'Store the row and column into G1 and G2 for the drop down loading
Call RowNum(target)
End Sub
[Code]....
how I can do this, as I cannot find reference to a Worksheet_Enter function ?
View 2 Replies
View Related
Mar 20, 2008
I have an Excel worksheet that when a user enters something into cell A1 I want to clear cells (b2:b20). If I use an object, I can attach a macro to it, but I don't want to use an object. I want to use an Excel cell and then when the user types into it, I want to perform an action.
View 9 Replies
View Related
Mar 24, 2007
How do I enter a value into a cell and divide that number automatically by another value all within the same cell.
For example..
I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.
View 3 Replies
View Related
May 18, 2007
I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
View 5 Replies
View Related
Jun 24, 2013
I'm putting together a userform and all the values and ending up in the correct place except for the two hyperlink cells.
I think it is because I've got the anchor wrong but Im not sure how to correct. I'll put the whole form in so if there is something fundametal going wrong in the early stages I can be put right, but the hyperlink issues are down the bottom at the Offset 16 and 17.
VB:
' Write data to worksheet
RowCount = Worksheets("Resources").Range("A2").CurrentRegion.Rows.Count
With Worksheets("Resources").Range("A2")
[Code]....
View 4 Replies
View Related
Sep 16, 2013
I have created a time sheet and I would like to know if there is a way to auto-lock cells after time is entered so that it cannot be changed. As of right now I have employees enter the time by using Shift+Ctrl+;, so the exact time is recorded, however employees can write in whatever time they want. so I guess what I want to do is 2 things:
1- can the cell be formatted to only accept Shift+Ctrl+;, so that the exact time is entered in the cell and prevent employees from entering times they were not at work?
and
2- can the cell auto-lock after Shift+Ctrl+; is entered so that they can't modify the time sheet the next day if they arrived earlier than the day before?
View 5 Replies
View Related
Feb 2, 2014
I have a worksheet, i want to lock the data entered in a cell of a particular column . e.g. if i will enter the data in A2, A2 should be locked, when user enter data in cell A3, A3 should be locked. I want this code to apply on multiple columns (A,C,E,G).
View 3 Replies
View Related
Mar 5, 2014
I have created a form where a date and remark is added every few days. I am using Phone Number as the base of VLookup. I want a code so that the date and remark which is entered in a Text Box, is stored in the first available blank cell pertaining to that employee. I want the first date and remark to be under "Date 1" and "Remark 1".
Thereafter, if another remark is subsequently added for that employee, the form would ask to enter his phone number to identify which employee the remark pertains to. Then the Text Box where remark is entered should be stored under "Remark 1" if there has been no remark before, "Remark 2" if "Remark 1" already exists, "Remark 3" if "Remark 2" already exists and so on.
I do not want to enter it in a new row. I want to enter the remark in the next blank cell of the row which matches the employee Phone Number.
Untitled.png
View 5 Replies
View Related
May 14, 2012
I need to enter =+c5,+c6,+c8,+c9 thru +c105 in a cell on5 different work sheets. is there an easier way than manually typing.
View 5 Replies
View Related
Sep 24, 2007
Is it possible to have this function work to round in the same cell the data is entered in? ...
View 9 Replies
View Related
May 17, 2007
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1".
Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
View 4 Replies
View Related
Jan 6, 2013
I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
View 2 Replies
View Related
Nov 9, 2005
I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.
However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:
=sheet1!A1
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)
View 10 Replies
View Related
Apr 13, 2013
I'm trying to make a schedule for some of my employees (300 to be exact) and I was trying to work out a way of speeding things up a bit. I've made plenty of pretty complex equations for working with the task but the solution for what I would like to do now alludes me.
Here's the challenge: I have a sheet where I would like to insert employees within row 4 and then have their shifts shown visually in the column below that, and have the hours they work put into a merged cell (or something similar), like what can be seen in the blue cell under "Employee 1" (C6). What I would like to do is just have something like a VBA detect that a series of cells is merged (red cells) and insert the corresponding time, located within B5:B33, into that cell (with same format as the blue cell). Also, above the employee's name I could put a button to run something like a macro to run the program for the corresponding employee directly under that button.
Essentially I want to merge a series of cells, hit a button, and have excel locate and input the correct times within that merged cell.
View 1 Replies
View Related
Jul 26, 2013
I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.
My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that
If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.
View 2 Replies
View Related
Feb 5, 2014
I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.
Example =Jan!M6, =Jan!N7, =Jan!O9 etc.
I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.
View 5 Replies
View Related
Oct 3, 2008
I would like to be able to click on the individual cells of a particular column in a spreadsheet and by doing so Excel will automatically enter an "x" into that cell.
The bigger picture is that users can search a spreadsheet for email addresses, an advanced filter posts the results to a separate sheet from which users can configure a group email by clicking an "x" next to the email addresses they want included. Essentially, it's kind of mimicking checkboxes.
View 2 Replies
View Related
Feb 21, 2007
I need a cell to read $0.00 always even if the cell is deleted. so if blank = $0.00.
Here is what I thought would work but doesn't.
Sub Worksheet_Change(ByVal Target As Range)
Range("b5").Value = 0
End Sub
View 9 Replies
View Related
