Alternative To Lengthy OR Statement?
Dec 30, 2009Is there a more efficient method to what i have attached? i.e. if E4=anything between B2 and B10 then C2 is displayed. Between B11 and B16 then C11 is displayed.
View 4 Replies
ADVERTISEMENT
Alternative To A If Statement
Oct 7, 2008I am trying to make an IF statement and need to enter 12 if's. Excel will only let me go to 7. Example, if Jan is in AE11 the copy the number from B96, if Feb is in AE11 then copy number from c96. Everything works up to July.
View 2 Replies View RelatedAlternative To An IF Statement
Jun 29, 2009im trying to figure out how to loop or if there is an alternative to an if statement in this situation here...
in cell b2 i need to say
"if the height =< 12 then return 0
if 15==21 then 1
if 24==36 then 2
if height >=39 then 3"
Alternative For Extra Long Nested If Statement
Sep 20, 2012I was trying to search for a set of keywords in column1 and if the logic is true then print predefined word in column 2. The problem is I have 14 sets of keywords for which I tried creating the longest if statement only to find out that the limit is 7. Below are few of the sets.
=IF(COUNT(SEARCH({"SAN","lpfc","scsi","adap","path"},S2)),"STO")
=IF(COUNT(SEARCH({"process","nim","root/bin","agent","genhkdly","script","dbsync_dly","vcs","listener","horcm","msoffline","cpu mdmprd"},S2)),"APP")
=IF(COUNT(SEARCH({"percent","inode","space","check-kerberos","nfs","file system","jfs"},S2)),"FLS")
[Code] ....
Alternative To The FileCopy Statement For Open Files?
Dec 8, 2008I'm sure I have seen a workaround/alternative (to FileCopy) technique for copying files that another user may have open at some stage in the past but when I google all I can find is examples of code looping until the file is closed & then performing "FileCopy source, destination".
Are there any alternative techniques that will allow a copy to be taken of a file on a shared drive that another user may have open without having to have my code "pre-open & savecopy as"?
The reason I'd like to do this is to allow for (5) network files to be copied to a local (c drive before performing various filtering/extraction actions on each file. The consolidation/summarising code I currently have runs in about 3 minutes* when tested on my C drive but "Network speed" (?) has caused it to take 30-40 minutes + when using the live network files.
The files are weekly Production schedules that are created/controlled by Site Planners & may still be open by a number of users for review when I wish to complete the consolidation (after Planners let me know that they are saved/set for the week).
*All possible "Application.xyz = false" precautions have been taken to assist with code speed & reset.
Lengthy IF Formula
Jun 12, 2009the following IF formula. Initially the formula was as follows:
View 2 Replies View RelatedMacro To Reduce Lengthy Financial Data...?
Jul 5, 2006I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).
View 5 Replies View RelatedDisplay Full Contents Of Lengthy Cell Upon Selection?
Jul 3, 2013I am working with a catering form that someone else created at work. It has a drop down list of menu items available. Once an item is chosen, the item description pops up.
These descriptions are so much longer than anything else in the row (item name, qty, #, cost). Is there an option to make the full text temporarily visible by selecting the cell, and leave it cut off the rest of the time? I am trying to keep the form at one page.
Alternative To OR
Dec 18, 2009I use an Array Constant in an IF statement, rather than using OR?
I have tried the following without success:
=IF(A1={0,1},"Something","Something Else")
Which would look like this using OR:
=IF(OR(A1=0,A1=1),"Something","Something Else")
Is it possible? If so, what's the right syntax?
Alternative For INDIRECT
Jan 26, 2009I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
SUMIFS Alternative
Jun 17, 2009I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
View 2 Replies View RelatedAlternative To SUMIF
Jan 21, 2010I'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.
View 2 Replies View RelatedIF Limitation - On Alternative
Jul 23, 2009I have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))
Exit Sub Alternative
Aug 3, 2009Exit Sub alternative. I have the following
View 5 Replies View RelatedVB Alternative To If Function?
Jun 4, 2009I use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.
IE:
Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..
I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.
An Alternative To Nested IF's
Sep 19, 2007I was creating a formula in excel yesterday that used nested IF's but I soon found out that the maximum number you can use is 7. So I did a bit of digging and found out that I would be better of using a Select Case in VB.
What I want to do is this:
=IF((SEARCH("case1",A1)),"text1",IF((SEARCH("case2",A1)),"text2",IF((SEARCH("case3",A1)),"text",...etc until I have listed every possible case and I want to do this for a range of cells A1:A1000 or however many entries I have.
I am assuming I would have to use code similar to the solution in this thread:
[url]
The only thing is I cannot translate this to what I am doing. Do I need to write a loop to go through the entire range of cells I want to check? How do I determine if a specific string is contained within a cell? Once the macro is written do I refer to it from a cell or run it from within the macro menu? Please bear with me as I am new to this and have not coded in VB since school.
Alternative To Comments
Jul 30, 2008For each cell, instead using a comment, I need another way to show a large amount of principaly letter texts, without using hypervincules, and ofcourse comments.
By the way, the same text I enter or modify put in this "way", I need to modify in the MSWord document and viceversa.
I appreciate the linking do not open another app, because the large amount of them I need to work with.
Loop Alternative
Sep 5, 2008Dim myRow As Long
Dim myCol As String
Dim Search As String
Sheets("Master Holdings").Range("H2")
Application.Screenupdating=false
mycol="E"
For myRow=6000 To 1 Step -1
If Activesheet.Cells(myrow, mycol).Value Search Then
Rows(myRow).Delete
End If
Next myRow
Application.ScreenUpdating=True
End Sub
Is there a better way to perform this code?
Alternative To Sumproduct...
Oct 9, 2009I have a sumproduct that evaluates # of conditions and returns the count when everything is met. But with the amount of data I have it is very slow to recalculate. I'm trying to find an alternative. I've read through a bunch of threads about different ways to avoid sumproduct but I can't get them to work. Here is the criteria I'm working with (simplified) .....
View 9 Replies View RelatedAlternative To Value Attribute
Feb 2, 2007I recieved on my last thread (link below), the code provided worked fine.
Previous thread I submitted
I now have a problem with the .value attribute. When comparing Excel finds two cells unequal in '.value' then writes '.values' in changes sheet. When using =A1=B1 the result is TRUE. I don't want equal valued cells to be picked up how can I stop this? (Macro code included in previous thread if needed)
Any Alternative To Loop Until
Mar 22, 2007I have this excel project that captures identification numbers using a scanner. once the scanner gets the info, it will mark the date/time the person was scanned. my problem is once the info scanned is not on the list, it will continue searching till i do an escape or ctrl-break.
heres the
Private Sub cmdOK_Click()
ActiveWorkbook. Sheets("list 2007").Activate
Range("F25").Select
Do
If ActiveCell.Value <> txtID.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = txtID.Value
ActiveCell.Offset(0, 2).Value = Now()
Range("F25").Select
txtID.Value = ""
txtID.SetFocus
End Sub
INDEX As An Alternative For INDIRECT
Feb 18, 2009In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.
It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.
However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function
This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.
Alternative To Pivot Table
Jun 22, 2009I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.
In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..
IF Command - Too Many Levels, Alternative?
Sep 15, 2009I am trying to use the command below to report what type of bonus is to be awarded. .05% - 1% bonus, commission based.
=IF(E7>=0.12,1,IF(AND(E7>=0.115,D11<=0.1199),".09",IF(AND(E7>=0.11,E7<=0.1149),".08",IF(AND(E7>=0.10 5,E7<=0.109),0.07,IF(AND(E7>=0.10,E7<=0.1049),"0.06",IF(AND(E7>=.095,E7<=.099),".5",IF(AND(E7>=.09,E 7<=.0949),".04",IF(AND(E7>=.085,E7<=.0899),".03",IF(AND(E7>=.08,E7<=.08499),.025, IF(AND(E7>=.07,E7<=.0799),".15",IF(AND(E7>=.065,E7<=.0699),".005")))))))
It is telling me there are too many levels. I am not an Excel expert, so I am trying to figure out an alternative to this command, I am sure one is available.
Alternative For SUMIF Formula
Jan 27, 2010The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance
Column E = Type of expenditure
Column D = Values
Column B = Payments
K23 = Currency conversion rate
Alternative For Paste Special
Jul 9, 2009During the execution of some code I come to a point where the properties of a range of cells needs to be converted to numeric (is at that point text).
Normally I do that by adding a 1 in an cell nearby and use the copy/paste special method.
I was wondering if I can do that in a different way.
Is it possible to define a MultiplyFactor as 1, and use this in paste special part.
Something like: .....
Alternative To Multiple IF THEN Statments
Jun 30, 2009Does the following formula make sense and if not, what do I need to change? I'm receiving a "#VALUE" error. =MROUND(IF(A9=1,SUM(D1*0.1), 0) (IF(A9=2, SUM(D1*0.1/2), 0)), (IF(A9=3, SUM(D1*0.1/3), 0)), (IF(A9=4, SUM(D1*0.1/4), 0)),6)
It is possible to have multiple IF,THEN statements in one formula? A9 and D1 are the variables driving the formula. Let me know if I need to provide a detailed explanation.
Alternative To Summing Nested If's
Oct 8, 2009I have a table of approx 20k customers (column A) and their $ sales (column B)for a time period. I need to sum the sales of approx 50 customers. I have the names of the 50 customers to be summed in one column so i can reference them and easily change out a customer if need be.
View 5 Replies View RelatedMacro With Alternative Option
Oct 1, 2008Just a curios question is it possible to have a button on a macro that if you press it would run one set of instruction, but if the user was holding a key down say the shift key and pressed the same button, it would run a second set of instruction
EG
If [Shift] key active then
Run command 1
else
Run command 2.
Alternative Formula For Xl2010
Feb 21, 2010Sheet2ABCDE1Start Date 01/01/201005/01/2010Holiday211/01/2010Holiday3Days1445the date to be back to work 19/01/2010Excel 2010
Worksheet FormulasCellFormulaB5=WORKDAY.INTL(B1,B3,16,D1:D2)
but how i can get the same result with Xl 2003 or 2007