Entering Value When 2 Criteria Are Met?
Sep 12, 2013
I'm trying to get a formula that will enter a particular value when 2 specific criteria are met. I've been trying to use an =IF(AND( statement, but so far I keep getting a "the formula you typed contains an error" message.
I need to have cell V9 return a value of 8 if the following conditions are met:
Cell D9 = "SG"
Cell H8 of a different workbook (Called Work Schedule) = 3
I would like the value of V9 to change to 12 if:
Cell D9 = "SG"
Cell H8 of 'Work Schedule' = 19
My formula now looks like this:
=IF(AND(D9="SG",'WORK SCHEDULE'!H8=3,8,IF(AND(D9="SG",'WORK SCHEDULE'!H8=19,12,""))))
View 6 Replies
ADVERTISEMENT
Jan 29, 2010
I am adding a sheet with a graph I made. There are two sheets, the first with the graph, the second with tables. What I would like to do is populate the graph based off what is entered into the tables. My main goal is to get the cells in the cells in the graph for each day to fill in color between the given time frame entered in on the table. Obviously I would be rounding up or down to make the time end in ":00."
The color I would like to fill the graph with is shown on the graph sheet. Also, if anyone can actually get this to work (I honestly wouldn't know where to start) Is there a way to have multiple colors and a possible overlay of colors?
Lets say on tuesday I have 10:00-15:00, and 12:00-18:00. Can I have two colors populate the cells and a thirs color populate the cells in the overlay which would be 12:00-15:00?
View 4 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
Oct 1, 2008
Has anyone come accross any smart and time saving alternatives to enter =NA() in any empty cells.
View 9 Replies
View Related
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
View Related
Dec 18, 2008
If I use format cells and choose time... do I have to enter the time in decimals to get it to show up like 1:30 PM? Or is there a way for me to type something quicker like 130 p?
View 9 Replies
View Related
Mar 2, 2009
I'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that!
View 14 Replies
View Related
Apr 9, 2009
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
View 14 Replies
View Related
May 4, 2009
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.
View 11 Replies
View Related
Aug 19, 2009
I'm building a database which I intend to upload to an ecommerce website. I am retrieving information on some of our products from the manufacturer's website. The information appears on the manufacturer's website as follows:
Part No : DP9970
* Description : Disc Pads
* Manufacturer : Trupart
* Unit Of Sale : Boxed Set
* Width (mm): 130
* Height (mm): 62
* Thickness (mm): 19
* Note: 4 pads with top clip
When I copy and paste this info into excel, it gives each individual line of information a separate cell. I need the information to appear in one cell in order for it to display properly on my website.
View 4 Replies
View Related
Oct 20, 2009
I need to find a way to enter times into excel without having to use a colon.
The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.
So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.
All my times will be entered into Column D.
I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.
Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?
I use Excel 2007, if it matters.
View 12 Replies
View Related
Dec 10, 2013
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.
View 2 Replies
View Related
Jan 8, 2014
I work in a fruit and veg business and they are using parchment and quill still, well it certainly feels that way. I have been asked by customers to provide an online order form which i will email to them and they can fill in and email back, i scanned an invoice and used OCR to upload it to excel and it has come out quite well.
Basically there are three columns of product lines and then directly to the right of each product is a column i have entitled Box & KG respectively, if the customer types a number into the box column i want it to automatically add 'Bx' to the end so this if they typed 5 in a cell in the box column it would look like this '5 Bx' (obviously without the quotes), and the same for the KG column but it would denote 5 KG instead, also i would want it to add in the space as well after the value.
View 3 Replies
View Related
Mar 12, 2014
I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.
12
24
36
48
510
612
7
8
9
10
View 5 Replies
View Related
Aug 1, 2014
I have a list of number I want VBA to add to a sheet. right now i have a VBA to add it one by one but was wondering if there was an easier way.
EX.Range("A1").Select
ActiveCell.FormulaR1C1 = "78"
Range("A2").Select
ActiveCell.FormulaR1C1 = "275"
Range("A3").Select
ActiveCell.FormulaR1C1 = "280"
Range("A4").Select
ActiveCell.FormulaR1C1 = "765"
View 1 Replies
View Related
Mar 22, 2014
I am a beginner in VBA programming and I have to make a Userform for entering data in the specific places(in a table) in the worksheet. But when I enter a number, it changes all the cells to that number. I can't find my error.
View 7 Replies
View Related
Apr 11, 2009
I have few sheets in a workbook and last sheet named report, I update each sheet date wise except report, problem is that the report sheet contains formulas of last rows sum, if I update today date report then I should change all formulas in reports. Kindly see attached file.
View 5 Replies
View Related
Apr 23, 2009
I have a macro that does the following.
-Every time a cell in Column D contains New or Old, “Customer” is entered into Column G in the same row.
-Every time a cell in Column D contains Old2, “Assets” is entered into Column G in the same row.
-Every time a cell in Column D contains New2, “ShortTerm” is entered into Column G in the same row.
-Every time a cell in Column D contains Old3, “LongTerm” is entered into Column G in the same row.
Here is the macro.
View 5 Replies
View Related
May 19, 2009
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.
View 10 Replies
View Related
Nov 23, 2011
If I have 3 text boxes textbox1, textbox2 and textbox3
I want ot be able to enter a time in textbox1 and then a time in textbox2 and textbox3 would give the difference. i.e. 09:00 17:00 then textbox 3 would calculate 08:00
Then all 3 times would automaically be entered in Sheet1 A1,B1 and C1
View 9 Replies
View Related
May 23, 2012
I will have to insert the below table in the worksheet area whereever I am working. Batch No.Doc. No.DateRemarks22-May-12
Fox example I require the "Batch No." in the ActiveCell and "Doc No." in the same row next column, "Date" in the same row and next column and "Remarks" in the same row next column. I also require the Today's date below the "Date", by using =Today() function of excel.
I will have to repeat the above table over 100's of times.
View 2 Replies
View Related
Jun 13, 2013
I need a VBA code for entering dates. The start date cell is A15.When I select cell A15 and hit macro button it inserts todays date and fills in 1-month of dates in column A excluding the weekends...
View 3 Replies
View Related
May 29, 2014
I have a formula that I'm using which works and gives me the right value.
Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)
However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.
Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"
View 3 Replies
View Related
Jun 6, 2014
I have tried to create a simple formula, but I am unable to make it work correctly. I need a * symbol in a column if the number to the left is greater than zero, and blank if left cell is also blank. My current formula that does not work displays the symbol if there is a blank or number in the left column. If this is needed, the formula A5 to the left reads another worksheet and displays a "" blank if no data.
Current formula that needs correction. B5 formula reads A5 and should enter "*" if >0 and "" blank if A5 is blank.
=IF(A5>0,"*","")
The current formula is incorrectly returning * if A5 is blank or if it has a number greater than zero. Correct formula that will return the correct results.
View 2 Replies
View Related
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
Sep 24, 2007
I have the bellow code, I'd like enter the input on the next available row on column "A" instead than on "A2"
Dim strResponse1 As String
strResponse1 = InputBox("First Name", "Hey you !")
If strResponse1 = "" Then
MsgBox "You have chosen not to participate!", vbInformation, "What happened?"
Exit Sub
End If
Range("A2").Value = strResponse1
View 9 Replies
View Related
Jul 29, 2008
I am currently working on a button which does the following:
User wants to create a new sheet
Clicks button
Input box prompts user to enter sheet name
User enters the sheet name
The sheet is created and copies over formats and values from another sheet
I then want the user to also enter a date.
The date must then be places (always in "C2") then each second column after that the date entered + one day.
E.g. C2 = 01/08/2008 then C4 will have 02/08/2008
I want this entered for the whole month.
The code is below:
Sub copyformula()
Dim vsheet As String
Dim vdate As Date
vsheet = InputBox("Enter a sheet name:")
View 9 Replies
View Related
Oct 20, 2008
My VBA sub contains
DIM sFormula As String
DIM rCell as Range
at some point in the code, the value of sFormula is set to something like
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)
When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.
View 9 Replies
View Related
Feb 13, 2009
i am a rowing coach and have to enter a lot of splits every day after practice and am hoping to find a way to do it without having to use the : or . keys.
so a split like 1min. 27.5sec, displayed as 1:27.5 could be entered as 1275.
i see that there are some questions already about this but seem to just work with H:M:S and time of day, i tried but wasn't able to use these for my purpose.
another fly in this ointment is that i also enter heart rates on the same sheet so in A1 it would be splits A2 heart rate, A3 split A4 HR.....
i do this for approximately 40 rowers each day with different workouts each day so each worksheet has splits and HR in different rows and columns.
View 9 Replies
View Related
Apr 26, 2006
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies
View Related
