Alternative To Lengthy OR Statement?

Dec 30, 2009

Is 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, 2008

I 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 Related

Alternative To An IF Statement

Jun 29, 2009

im 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"

View 9 Replies View Related

Alternative For Extra Long Nested If Statement

Sep 20, 2012

I 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] ....

View 3 Replies View Related

Alternative To The FileCopy Statement For Open Files?

Dec 8, 2008

I'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.

View 3 Replies View Related

Lengthy IF Formula

Jun 12, 2009

the following IF formula. Initially the formula was as follows:

View 2 Replies View Related

Macro To Reduce Lengthy Financial Data...?

Jul 5, 2006

I'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 Related

Display Full Contents Of Lengthy Cell Upon Selection?

Jul 3, 2013

I 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.

View 2 Replies View Related

Alternative To OR

Dec 18, 2009

I 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?

View 9 Replies View Related

Alternative For INDIRECT

Jan 26, 2009

I 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?

View 11 Replies View Related

SUMIFS Alternative

Jun 17, 2009

I'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 Related

Alternative To SUMIF

Jan 21, 2010

I'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 Related

IF Limitation - On Alternative

Jul 23, 2009

I 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))))))))

View 14 Replies View Related

Exit Sub Alternative

Aug 3, 2009

Exit Sub alternative. I have the following

View 5 Replies View Related

VB Alternative To If Function?

Jun 4, 2009

I 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.

View 3 Replies View Related

An Alternative To Nested IF's

Sep 19, 2007

I 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.

View 9 Replies View Related

Alternative To Comments

Jul 30, 2008

For 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.

View 9 Replies View Related

Loop Alternative

Sep 5, 2008

Dim 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?

View 9 Replies View Related

Alternative To Sumproduct...

Oct 9, 2009

I 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 Related

Alternative To Value Attribute

Feb 2, 2007

I 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)

View 9 Replies View Related

Any Alternative To Loop Until

Mar 22, 2007

I 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

View 7 Replies View Related

INDEX As An Alternative For INDIRECT

Feb 18, 2009

In 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.

View 7 Replies View Related

Alternative To Pivot Table

Jun 22, 2009

I 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..

View 14 Replies View Related

IF Command - Too Many Levels, Alternative?

Sep 15, 2009

I 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.

View 5 Replies View Related

Alternative For SUMIF Formula

Jan 27, 2010

The 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

View 2 Replies View Related

Alternative For Paste Special

Jul 9, 2009

During 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: .....

View 9 Replies View Related

Alternative To Multiple IF THEN Statments

Jun 30, 2009

Does 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.

View 4 Replies View Related

Alternative To Summing Nested If's

Oct 8, 2009

I 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 Related

Macro With Alternative Option

Oct 1, 2008

Just 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.

View 9 Replies View Related

Alternative Formula For Xl2010

Feb 21, 2010

Sheet2ABCDE1Start 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

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved