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.
I really DO have a problem. I need excel to create me a list of whenever someone enters their name. Now, this may sound simple, but, the text in the list CANNOT be allowed to be overwritten.Also the text needs to be entered into the NEXT empty cell between C30 and C50. This is basically so I can keep a record of whenever anybody does anything/modifies the cells that include text between S7 andS21
So basically if text is ever entered into S7 all the way down to S21, I need an automatic copy created into the next available cell between C30 and 390. It is also ok if there are duplicates values(names).
I would give a print screen, but I don't know where the attach button is on this forum.
In simplicity: We want to be able to enter a value in cell A1 (and hit enter), but we want the value in A1 to be increased by the value saved in H1.
So: if H1 had 250 saved in it, if we enter 150 in A1 (and hit enter), we want the value in A1 to become 400.
I have two files which are attached.
One is the main report file "ops report.xls" and other is the raw file (Air.xlsx) from which data is to be added to this file. i manually take the data and enter it into the ops report everyday. This i want to automate as there are many such sheets to be completed (i have mentioned just "Air" sheet here in ops report).
Now what exactly i want to do is?
take data from Air.xlsx workbook as per the columns in the Air sheet in Ops report and paste it on that particular date row. this i will have to do everyday so the nest day the data will be pasted on next row against the date. The yellow columns which i have marked have formula so you need not touch that columns.
I nee to paste the average of Air sales and Air service. so we have to take average of row 43 and row 89 for first two columns in ops report. and then average of row 44 and row 90 for column L,N,O,P,Q in the ops report.
Afterwards the average of column J must be pasted in "Interval" sheet in ops report.
This is a bit tough but i know there are many genius people out there who can easily solve this.
I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedI remember years ago that I made a couple formulas that on sheet one would transfer to another sheet when I enter in sheet one.
View 5 Replies View RelatedIn a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
I have a lot of cells containing numbers. Some numbers are small, some are big.
I want to divide all numbers which are greater than 300 by 1000.
IF cell A2 > 300 THEN divide by 1000.
How to do that? Is it possible to convert value in the same cell like using conditional formatting principle?
I am trying to do:
Say value in A2 and A3 is 4.5 and 5 respectively
and the value in A1 is and 1 respectively
B2 and B3 have zeros.
Case 1 :
Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.
Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on
Case 2 :
Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1
Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...
I am using the below formula -
For CASE-1 : C2 is
=IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)
D2 is
=IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)
and so on...
But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.
For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)
D3 is
=IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)
When the numbers in A2 and A3 are decimals and not the whole numbers.
I am trying to write a macro where I divide the value of one cell by that of a second cell, multiply it by 500 and then to paste the outcome of the formula to a third cell. This is what I wrote
View 5 Replies View RelatedI have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View RelatedI have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.
test sum skip blank cells.xlsx‎
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?
Column (B) contains two different units, either (ug L-1) or (mg L-1). I have this formula, =IF(B2="(ug L-1)",A2/1000,A2), which copies the value in column A to column C, so for cells that are in (mg L-1) units, it's a direct copy, but for cells that are in (ug L-1) units, it divides the value in A by 1000. However, I can't remember what the formula should be if the cell contains a "<" symbol AND is also in (ug L-1) units.
e.g.
< 0.004(ug L-1)
How can I change that to <0.000004?
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)
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 RelatedI 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 RelatedIn 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?
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!
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).
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 Related0: 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.
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?
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
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??????
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 ?
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 RelatedI'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 RelatedI'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]....