Excel 2010 and the cells are formatted to time
Cell G1 = >8:45 want Cell H1 to deduct 45:00 minutes
Example Cell G1 = 10:00 want Cell H1 to show 9.15
Example Cell G1 = 9:00 want Cell H1 to show 8:15
Example Cell G1 = 8:00 want Cell H1 to show 8:00 no deduction
Example Cell G1 = 6:00 want Cell H1 to show 8:00 no deduction
Example Cell G1 = 0 want Cell H1 to show 0
I have below format in excel and i will be updating scores for each parameter (column B).
I will be inserting a command/click button and when i click on the button macro should take the name, contact No., scores from column B and paste it in sheet 2 in the same workbook in one row, in the same way when I click the button the scores should be taken from sheet 1 column b and paste one below other row in sheet 2 each time i click on button.
Below is how raw data looks like.
Column A
Column B
Name:
ABC
Contact Number:
XYZ
Parameter
Score
[Code] ..........
Output expected in sheet 2 is as below.
Name:
Contact Number:
Parameter A
Parameter B
Parameter C
Parameter D
Total Score
ABC
XYZ
2
5
6
7
5
I am using Excel 2010.
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?
View 9 Replies View RelatedI have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10
View 2 Replies View RelatedI have some excel files which should be saved in Excel 97-2003 Workbook format . I am trying to write vba code for that i am struggling at starting . problem is i have 6 excel files which should be saved as Excel 97-2003 Workbook format i don't know how i give 6 files path to code .
View 2 Replies View RelatedI'm currently trying to get a Index Match function that finds a cell with given text in it, then returns a value that corresponds to a cell with that same text plus the word 'Sum'. I cannot use the offset function because the Sum cell appears at the end of an expense breakdown, and each category has different numbers of expense items.
Is there an easy way to do this?
I've been looking all over for the most basic of VBA codes to insert a timestamp in a single cell (B1) when cell A1 changes due to formula result change. All the answers I've found are for manual updates of A1.
A1 has the simple formula: =SUM(F1:F10000)/3. I would like cell B1 to insert a new timestamp when the results of this formula in A1 change. On a weekl basis, I will paste-value data into the whole F column, which will change the resultes in A1.
If this can't be done, or is too complicated (I don't really write VBA, only copy and paste basic code), is it possible to have a timestamp inserted into B1 based on the paste-value event into the F column?
Excel 2010
I am using Excel 2010 .I have set up Data validation for a dropdown box so I can select from a list of items. In the old versions of Excel the actual drop down arrow used to appear in each cell. In the version I have, the drop down arrow only appears when you select the actual cell. When I did the validation I checked the " In-Cell Dropdown", but it still doesnt put the arrow in the cell. Is this functionality available in Excel 2010 ?
My second issue is a formula.
The last name is in a list of items and users have to select Yes or No to theitems on the list. I am wanting to create another spreadsheet that automatically populates based on their responses.
In short, I want to be able to set up a rule or formula that states if the answer in column A is "y" then I need the information in column B to be displayed.
The ultimate aim is to get a automatic sub set, (in another tab), of the orginal information based on users responses.
I have Excel 2010. I am creating a spreadsheet for server backups. I have always been told to take the current size of the disk and multiply it by 10% to figure out the backup size needed for the backup. I am trying to create a formula to do this. I have a server with 25GB. The formula i tried was
=a1*10%. A1 being 25gb. Excel does not like the GB in the cell also this is only giving me the difference. I need to know what size to allow for my backup on this server.
I'm trying to create a formula that states. If tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000 or if the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absolute value of 20% or 100,000 or if the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000 then Y OR N.
I figured out the absolute value portion of the formula but I'm not really sure how to combine that with the tank maximum capacity piece.
=IF(OR(ABS(T14)>10%,S14>20000),"Y","N")
I've been given a formula to use (embedded in an add-in that is password-protected). One of the parameters REQUIRES quotes.
The formula goes like this: =MYFORMULA(1,2,3,"ABC")
If I put ABC in a cell (say C5) and use the formula =MYFORMULA(1,2,3,INDIRECT(C5)), the formula doesn't work.
If I put "ABC" in cell C5, the indirect function still work make the function work.
Even if I have ABC in cell C5 and use =MYFORMULA(1,2,3,""""&C5&"""") or =MYFORMULA(1,2,3,""""&INDIRECT(C5)&""""), these won't work.
I wish I could just change the UDF behind this, but that isn't possible.
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
I have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx
I would like to create a function where I would be able to ask and answer 20 questions. When using If statement it only allows me to use up to 7 nests.
I have created an example below with all the statements I need using IF but of course this doesn't work so I would need so how to handle this particular issue with so many questions/answers in one. Even though the delta between 5-4 and 3-2 is same I want different answers. I'm using Excel 2010.
=IF(AND(AK58=5;AL58=5);"OK";IF(AND(AK58=5;AL58=4);"1 OP";IF(AND(AK58=5;AL58=3);"2 OP";
IF(AND(AK58=5;AL58=2);"2 OP+1 NV";IF(AND(AK58=5;AL58=1);"2 OP+2 NV";IF(AND(AK58=5;AL58=0);"2 OP+2 NV+INST";
IF(AND(AK58=4;AL58=4);"OK";IF(AND(AK58=4;AL58=3);"1 OP";IF(AND(AK58=4;AL58=2);"1 OP+1
[Code]...
When I open Excel 2010, the formula bar appears about 6 times as tall as normal. I have not been able to find any way to reset it to normal size.
View 11 Replies View RelatedExcel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
What will show in column B will change each day as a I am creating a ordering picking list and want to show a pic in column A of the item to pick the item. Is there an easy way of doing this.
I have been messing around with the following
=showpic("C:DesktopItems46004978Thumb.jpg")
And thought I may be able to show this as by doing the following in the desired cells
=IF(ISBLANK(B1),"",INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0)))
On sheet1 I have all the model numbers next to
=showpic("C:DesktopItems46004978Thumb.jpg")
And the index formula will grab the correct showpic string depending on stock code that is showing in various column B cells but this isn't working.....
I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies View RelatedI have Excel 2010 & Windows 7
I am using the following formula to copy the name of the tab - =MID(CELL("filename",C26),FIND("]",CELL("filename",A1))+1,6)
I have a marco that creates a tab from a Master file, and I need to copy the name of the tab into cell A2, using the formula above. Then I use a vlookup on this cell, to get the data needed for about 10 other fields. I have these vlookups in the Master file. The vlookup doesn't work all the time. I get a #NA answer. Sometimes it does work. When I manually type in the project number on the tab, the vlookups work.
Sample of the vlookup: =VLOOKUP(A2,'AC-Program'!$A$1:$H$58600,2,0)
I was trying to find a way to use a value in cell A2 instead of the formula above, but I don't want to have to manually update it, I need it to be generated when the Marco generates the tabs.
Here is my macro:
Sub CreateSheetsFromAList()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")[code]......
Is there a way to add this function into this macro, and it will leave this field as a value instead of a formula?
In excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
How do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.
I have attach an example : test.xlsx
I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2
Variable 2:
Internal
External
Variable 3:
Existing
New
Or am I better using a VLOOKUP or something?
I have attached an example workbook where I have three worksheets i.e. Data1, Data2 and Master Data all of which have the same data format in each column
I have data in the Data1, Data2 tabs and was wondering if it is possible to copy the data from both these tabs (using formulas) onto the Master Data bearing in mind the rows of data in the Data1, Data2 can vary on week to week basis.
Excel version 2010
Attached File : Data.xlsx
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
