Spit Out A Specific Value
Mar 27, 2008
Column A contains 5,720 cells. Each cell has a number ranging from 1 to 300.
Columns B, C, D have data not relevant to my question (at least, I don't think it's relevant, anyway, except that they need to stay next to what's in column A; like if the word "Thank" is in column B next to the word "You" in column C next to the words "Very Much" in column D, and all are next to - in the same row as - the number "14" in column A, everything needs to stay that way throughout. Does that make sense?)
What I'm looking for is a formula that will spit out a specific value in column E that corresponds to the number in column A. There are 300 different values, ranging from 375-1. For example, I need Excel to find all the "1"s in column A, and spit out a "375" in column E (in the same row as the "1"). Likewise, I need to find all the "2"s in column A and put a "370.76" in column E. I need a "366.54" for all the "3"s; a "326.34" for all the "4"s, and so on.
Is there a formula that will do that, and, if so, how do I use it? Again, I'm not very familiar with Excel (terminology, which characters mean what, etc.),
View 9 Replies
ADVERTISEMENT
Mar 26, 2008
What I've got is various numbers (1-300) in column A, and what I want is a formula that will spit out a value (as assigned by me) for each number into column E. Like, I want Excel to find all the "1"s in column A, and show a "375" in column E in each row that there's a 1, and find all the "2"s and show a "376" in column E, all the "3"s, "4"s, "5"s and so on. I thought I'd be able to use the "IF" function, but apparently I can only use 64 different values in the formula, and I need 300.
Is there a formula that will do that? And, if so, how do I use it?
View 10 Replies
View Related
Feb 7, 2013
I've created an excel document to count calories. Here's an example of what I've made...
Item
Quantity
Servings
Cal
Fat
Carbs
Protein
Milk
8 oz
1
150
8
11
8
[Code] .....
MACROS
31.4
41.1
27.4
So basically I'm wondering if there's a way I could simply type in "milk" and have the rest of the information appear (cal, fat, carbs, etc). What would be even better is if I could somehow enter the item and create a formula to multiply by the serving size. I feel a word bank with a list of common food items I eat throughout the day and there respected numbers would be the most simplistic way.
View 1 Replies
View Related
Apr 16, 2014
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
View 2 Replies
View Related
Nov 7, 2008
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
View 9 Replies
View Related
Apr 21, 2006
I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.
View 5 Replies
View Related
Oct 9, 2009
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies
View Related
Apr 4, 2013
Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
View 6 Replies
View Related
Jul 1, 2014
Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.
Code in plain english would look like this:
For EACH cell in range A1:BZ500 meeting the following criteria:
Cell value is a date
AND
Cell's date is at least a week or more in the future
AND
Cell background (Fill) = RGB color code: (R:191 G:191 B:191)
DO the following:
Call repeatingsub
View 11 Replies
View Related
Apr 29, 2013
I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.
A
B
1
Account
Company Information
2
Account
Company
[Code] ........
View 2 Replies
View Related
Mar 26, 2014
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
View 1 Replies
View Related
Nov 30, 2007
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
View 3 Replies
View Related
Jun 18, 2014
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx‎
View 6 Replies
View Related
Sep 14, 2008
I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
View 14 Replies
View Related
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
View 5 Replies
View Related
Jun 21, 2006
I need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
View 7 Replies
View Related
Mar 13, 2008
Is there a way to make a macro convert '1' to Yes and '0' to No? I have 2 columns (F and G) in a worksheet that contain the value of 1 or 0, but I need to convert them to Yes or No. I have tried some fo the samples but they pop up a msgbox which is not required.
View 2 Replies
View Related
Mar 24, 2014
I'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.
After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.
View 1 Replies
View Related
Aug 14, 2009
let say in cell A6 i have a list
SnG
Cash
MTT
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
View 12 Replies
View Related
Apr 30, 2007
I'm trying to use a conditional format on a range of cells so that if there is more than 1 "A" contained in any given row, the cells containing the "A"s are highlighted (e.g. red)
I've tried:
=COUNTIF(B2:K4,"A")>1
but it doesnt work & now I'm stuck.
View 9 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Mar 3, 2007
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 Replies
View Related
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
View 3 Replies
View Related
Jan 20, 2009
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
View 3 Replies
View Related
Dec 3, 2013
I have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".
I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.
I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.
I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.
View 6 Replies
View Related
Nov 13, 2008
I have been trying for two weeks to look for excel function that can copy specific rolls of a worksheet to a specific rolls of another worksheet. I do not know the best way to explain the problem here. I have attached an excel, sheet 1 is the original data while sheet two is a sample of how i want the new sheet to look like after applying an excel function.
View 10 Replies
View Related
May 30, 2014
I currently have a protected worksheet which prevents the user from inserting row.
User however will be able to add a new row via Userform.
I was able to add a new row by this method, but for only after the active(selected) cell.
[Code]....
However, in the user form, i have a textbox1 and the value of the texetbox1 will dictate where the new row should be created. So for ex, if textbox1 = 10, the new row should be created after row 10 thus becoming row 11.
So i tried the following code and am having no luck at all...
[Code] ....
As a secondary question, after the new row is created, is there any way to populate the cells in the newly created row with values from the texboxes from the same userform?
So for ex, textbox2 = "This is Document" then C11 should read This is Document
View 7 Replies
View Related
Jun 3, 2014
I want to run an event for specific user. In this case, only John Doe should be able to run this command. It works fine if i put john doe in there and i get the MSGBOX since my windows log in is not John doe. But when I put my windows login there (and only I can run it), i dont get any msgbox or the rest of the command does not run..
View 10 Replies
View Related
Oct 25, 2009
I would like to make a code that does the following:
If a specific cell ("N47") changes it's value (not just has a value or a value over or under a specific amount. It needs to effect the dependent cell only when it changes it's value)
Then
Cell("K40") = Now()
"N47" is never typed on (so I can't use interesect) it is a formula (sum of D2:D30). It derives it's value from the other cells.
"K40" is also never clicked on. I just want it to show the date and time that "N47" changed it's value.
View 7 Replies
View Related
Apr 19, 2012
How can I get just the inner Folder (red) in this path? Example given.
C:Working_DirectoryPersonalFolder
View 2 Replies
View Related