Using Letters In IF Statements To Trigger Computations?
Mar 4, 2014
i'm attempting to create a spreadsheet that will enable me to calculate the number of certain materials required to construct new rural fencing. I have 4 different 'types' of fencing each requiring different levels of materials required. For example, a Type 'A' fence, requires 5 'droppers' per 10m span of fencing, whereas a Type B requires only 1, a Type C also requires 5 - but a Type D does not require any.
Here is what I have attempted to generate so far - but is giving errors;
=IF(E42="A",((E35/10)*5),0),IF(E42="b",(E35/10),0),IF(E42="C",((E35/10)*5),0),IF(E42="d",(0),0)
Cell E35 is a fence length field to compute that number of droppers per 10m span of fencing.
Cell E42 is the cell for fence type (i.e.: A, B, C or D).
View 12 Replies
ADVERTISEMENT
Jul 1, 2014
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
View 2 Replies
View Related
Nov 22, 2008
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies
View Related
May 26, 2008
Sheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
View 4 Replies
View Related
Jul 29, 2014
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
View 13 Replies
View Related
Dec 7, 2009
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
View 5 Replies
View Related
Jul 24, 2012
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
View 3 Replies
View Related
Mar 9, 2009
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name
View 11 Replies
View Related
Nov 17, 2009
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
View 11 Replies
View Related
Oct 14, 2009
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
View 2 Replies
View Related
Dec 17, 2007
how to trigger a message box?
if i type TP123 in cell A1 i want it to trigger a message box with a comment
View 9 Replies
View Related
May 20, 2013
I am preparing a list of calibration items that require yearly calibrated, how can I set the date and prompt me example 1 month ahead when the item is going to due soon.
View 3 Replies
View Related
Sep 17, 2013
I have attached a sample work book.
What I want to achive is the colouring of the cells in columns A-L using the trigger of the "Y" character in colums J-L
So when a Y is put in column J the cells to the left and including column J change to green. Then when a Y is put into column K the cells to the left and including K turn yellow. Finally when a Y is entered in column L the cells to the left and including L turn the lovely shade of Pink. It is possible the process will go from a Y in column J to a Y in column L mising out column K but I don't suppose this will matter.
I used to have it working in office 2003 to a fashion but have not yet got my head around 2010
View 7 Replies
View Related
Feb 26, 2014
I have a chart with 2 Y axis. I am attempting to write some code that will update both axis with the same max & min value that is triggered by the combobox selection. The code will update the axis but is not triggered by the combobox selection.
[Code] ...........
View 6 Replies
View Related
Feb 22, 2009
I want to trigger a macro that refreshes a pivot table but I only want to trigger the macro after 15 seconds. The reason is that I am pulling the source data from access mdb so I want only to refresh the data once the data is pulled.
View 3 Replies
View Related
Oct 20, 2012
How do I amend this formula to have it only trigger on a certain month.
=SUMIF(C:C,1,F:F)
Date Description CatCost feesTotal Balance
20/02/12 PayPal Credit 10$0.10 $0.00$0.10 $0.10
20/02/12 PayPal Credit 10$0.01 $0.00$0.01 $0.11
26/02/12 Payment 1$174.69 $4.49$170.20 $170.31
I need it to look at what month it is and then the category.
View 2 Replies
View Related
Jan 25, 2014
Is it possible to trigger a macro on a condition like this?
If date more than 1 week arrange date in sequence order.
View 2 Replies
View Related
Apr 5, 2007
I am having trouble trying to get an MS Access Query to run from MS Excel automatically.
I am trying to create an automated trigger in MS Excel that will automatically run my Access query by the times I specify in my statement.
For example: I need to run a query in Access at 9am, 12 pm and 10pm, that's it, but I believe I need to do it via Excel, I don't want the data returned to Excel, I just want Excel to execute the query at those specific times!
View 9 Replies
View Related
Jun 21, 2006
A1 is a drop-down list, created from Data> Validation>List, which lists 3 different words (Text1, Text2, Text3). I have recorded 3 macros (Macro1, Macro2, Macro3). Here's what I want to happen:
When Text1 is selected in A1, I want Macro1 to run (same for Text2/Macro2 and Text3/Macro3). I want the user to be able to change this value as many times as they wish and have the corresponding Macro run each time. I've tried creating the appropriate code in Editor using other threads on this forum, but I can't seem to figure it out.
View 8 Replies
View Related
Aug 2, 2006
I am encountering a strange situation with my Excel 2000. I have a public function, in a module in the VBA project associated with my workbook. But I'm not calling it from nowhere inside the code, or from other macros - it is not being referred anywhere in the workbook. Yet, after I make a slight change in code and not save my changes, when I return to the workbook and select a value from any cell with a validation-list (regardless of the sheet where it resides), that particular function is being executed!
View 9 Replies
View Related
Mar 22, 2007
I have already use excel web query, and set every 5 minutes auto update web.
And here is question , I want to use vba event to trigger when cell's value changed.
Unfortunately,
Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
This code didn't trigger successfully,I think that web query make cells
value changed didn't trigger the event.
View 3 Replies
View Related
Jun 14, 2007
Is it possible, that while running code that the code can say initiate the click event on a command button on another sheet.
Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"
Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?
View 9 Replies
View Related
Mar 4, 2014
We have a customer rebate in place with various levels of refund based on the quantity purchased during the year. I have used a sumproduct formula to calculate this before.
The customer used to have the following set up -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £3.00 per unit rebate.
So if they bought 2501 units they would get a rebate of (1000*1)+(1000*2)+(501*3). However the customer has trigger points so rather than the above it is now -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £4.00 per unit rebate if 2500 bought.
So now it would look like this - (1000*1)+(1000*2)+(501*4). However if they only bought 2499 units it would be (1000*1)+(1000*2)+(499*2).
View 3 Replies
View Related
Jul 28, 2014
I have cut and paste some code provided by members of this great forum to insert a row in a second worksheet at the same row number when one is inserted in the active worksheet. e.g. If I insert a new line at Row 14 in worksheet "admin" I also get a new row at Row 14 in worksheet "report".
Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Set sourcebook = ThisWorkbook
Set sourcesheet = sourcebook.Worksheets("admin")
Set targetbook = ThisWorkbook
Set targetsheet = targetbook.Worksheets("report")
myRow = ActiveCell.Row
targetsheet.Activate
ActiveSheet.Rows(myRow).EntireRow.Insert
sourcesheet.Activate
End Sub
However, anything I do in worksheet "admin" triggers a new row to be inserted in "report".... If I change text in any cell, or make any changes at all, I get a new row in "report".
Is there a way to restrict this action to only a line insert?
View 7 Replies
View Related
Oct 21, 2008
I need to trigger a hyper link say "Modify" based on the "True" Value of a "IF" condition in that sheet.
We can place that "IF" condition Right next to the Hyperlink if needed. But how to do that.
the If condition is like
=if(A1=B23, Trigger Hyperlink,False)
The cells are dynamic and even the sheet is Dynamic.(they can be any cell and any sheet)
View 9 Replies
View Related
Dec 5, 2008
When I open my workbook it also opens a separate workbook and hides second book.
when I close myworkbook I want to unhide the hidden one and close it without saving.
View 4 Replies
View Related
Feb 18, 2009
is possible to trigger a macro at the start of every month. I have a fairly simple bit of VBA, but just want it to execute on the 1st of every new month.
View 3 Replies
View Related
Dec 30, 2009
I have a series of macros altering various workbooks and sheets. They're numbered Step1, Step2, etc. In my Step5 I have a series of Case statements in a macro, and I don't know how to get it to do what I want next. I have NOT tried running this code yet, and I'm sure I have something(s) wrong in it.
1) If the selection in DstWbk, sheet "Steps", is "01DSP" through "11DSP" the macro needs to delete specific columns in the SrcWbk, and then move to the next step (6).
2) If the selection in DstWbk, sheet "Steps", is "*DSP" the macro needs to go directly to the next step (6)
3) If the selection in DstWbk, sheet "Steps", is anything else the macro needs to flash a generic "No Data found" message and move on to Step7.
The part of the code that's throwing me begins at 'Select only the specific regional data' and ends at the "Case Else MsgBox"
View 3 Replies
View Related
Jun 5, 2007
hi i am currently trying to construct a date based maintainance sheet
i will try to explain what im trying to do
in one cell date is inputted
the next cell automatically adds +30 to generate next test date
what i am trying to do is get the sheet to
there are 4 testers so its a 3 month cycle for each ie
tester 1 jan
tester 2 feb
tester 3 march
tester 4 april
tester 1 may
tester 2 june
you get the picture? i hope
so if
cell A1 todays date
a2...................................b2......................................c2
21 jan.............................21 feb................................tester 2
inputed ...................... =A1+30
and date
formated
cell
i am trying to get the formula to tell me after the inputed date has been changed who the next tester is
i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly
View 9 Replies
View Related
Nov 22, 2011
Is there a way to reference the controller that triggered a subroutine??
like the ME or ThisWorkbook, but for controllers on userforms.
View 2 Replies
View Related
