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
ADVERTISEMENT
Feb 26, 2009
I need to use the "IF" formula on a worksheet with 15 variables. There is a limit of 7 that can be used at once, does anyone know a way around this?
View 6 Replies
View Related
Sep 20, 2007
Here it is:
I used ceiling and floor for this but it was futile.
I was hoping to round off a no. according to the limitations set,
For example:
Sample Entries Preferred Rounded-off nos.
Cell A1 Cell B1
.40 .40
.39 .40
.38 .40
.37 .35
.36 .35
.35 .35
.34 .35
.33 .35
.32 .30
.31 .30
.30 .30
When using ceiling and floor formulas there was an error prompt telling me that there are too many arguments.
View 9 Replies
View Related
Jul 20, 2006
I have a spreadsheet with a drop down box with 30 names in it. On the other worksheets of this file I have budget figures.
What I need is a correct formula so that when I select a name from the drop down box it will grab the figure from a cell in another sheet and place it into the sheet where the drop down box is.
Is it possible to use a formula which grabs more than one cell?
I have been using this formula below:
=IF(B5=1,NNO!C8,IF(B5=2,NSO!C8,IF(B5=3,NWE!C8,IF(B5=4,NCN!C8,IF(B5=5,NCS!C8,IF(B5=6,NHO!C8))))))
It works for one cell at a time- but only allows 7 variables at a time and I have 30.
Which means I would need 4 or 5 drop down boxes in stead of one.
View 8 Replies
View Related
Sep 6, 2007
Please see the attached GIF.
I am trying to take information from columns J ("Family Size") and L ("Annual Income"), and figure out how many units should be assigned to columns N ("Very Low Income Units") and O ("Low Income Units").
Essentially, this is what I want to do:
If J2 = 1 and N< 22850, then N2 = F2 and O2 = 0.
If J2 = 2 and N< 26100, then N2 = F2 and O2 = 0.
If J2 = 3 and N< 29400, then N2 = F2 and O2 = 0.
And so on through "8."
I looked at the Nested IF Limitation suggestions on the FAQs . . . it didn't seem like CHOOSE or VLOOKUP would get me where I need to go. Unless VLOOKUP is somehow merged with IF. Does anyone know the easiest way to get this done?
Steven Auto Merged Post;Oops . . . forgot to attach. See attached.
View 9 Replies
View Related
Sep 11, 2013
i have couple of textbox in my userform and i want to limit them for 7 number and one letter e.g. 7777777X.
View 2 Replies
View Related
Feb 20, 2014
I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function.See Below... The function works if i use 7 IF statements.
=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),
IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),
IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),
[Code] ............
View 9 Replies
View Related
May 8, 2008
i have tried to create a userform thru which data is to be entered. so that the data will be printed on a worksheet. here there are few text boxes, in which the number of digits should be equal to 14. after filling the userform when i click the print button if it is less than or more than 14 a pop up msgbox should be displayed with OK button and the cursor should go back to that particular text box. i have written the following code, but it has a problem. even if the total no of digits are 14 the msgbox is displayed.
say the text box name is Roll no
If txtRollNo.MaxLength 14 Then
MsgBox "Roll No should be of 14 digits", vbInformation + vbOKOnly
txtRollNo.SetFocus
Exit Sub
End If
View 9 Replies
View Related
Sep 2, 2009
I am working on Microsoft Office Standard Edition 2003 excel and am having trouble with my coniditional formats. I need to have 4 but can only have 3. Is there a simplae way around this? if not let me describe what i want to do and maybe there is a coded solution.
In columns N, R, V and Z i will have % increase in sales over a period. Below 0% would be red, between 0-5% is Amber, 5-10 is Green and over 10% is Purple. Is there a simple code that can be applied to this?
I can define the paramteres in excel in the row columns below.
ABC
1Range1Range2
2red0%
3amber0%5%
4green5%10%
5purple10%
View 9 Replies
View Related
Feb 15, 2010
I had a happy macro that copy and pasted sheets in an output folder for all excel files from a target file that included the desired changes. it worked fine. then i realized that when pasting a sheet, the text is limited to 250 characters, so an important portion of my sheet (some instructions in various cells) was not coming over. So i created the following which will copy the columns, but some of the row formatting is different and doesn't line up. my questions:
-is there a better way around this text limitation?
-if not, is there a way to copy the row formats for this section and include in my macro?
-is there a way to copy the cells (all or a range) themselves while using "ThisWorkbook", thereby including all the appropriate formating? it seems to not like to do that.
‘Copy columns from target sheet
Sub Perf_rev_subfolder_replace()
'Update the constant here if necessary
Const sOutputFolder As String = "M:ODPerf review2010 oolsTest folder est"
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
For Each fl In CreateObject("scripting.filesystemobject").GetFolder(sOutputFolder).Files
If InStr(fl, "3.0") > 0 Then
With Workbooks.Open(fl..............................
View 2 Replies
View Related
Aug 2, 2006
I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.
I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?
Sheets("main").Select
ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text
....
email.Body = messagebody
View 6 Replies
View Related
Apr 27, 2007
I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.
Example:
Sheet1
A1 (text string value) = LOWER
B1 (text string value) = L1
C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)
A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.
Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.
View 9 Replies
View Related
Jul 14, 2009
how do i set the macro to read a range without limitation?
currently i have a recording macro that only reads to P126 - if i end there data in C127:N127 I would like the macro to recognized that there is new data and update to P127 with the new calculation....
View 9 Replies
View Related
Oct 20, 2006
I was sure it would be in your archives but couldn't find it - apologies if this is a FAQ.
I am trying to extract the data in a spreadsheet to a bunch of files, one file per cell. The cells I need have very long text strings in them, actually whole HTML files.
Using this
Dim HTML As String
HTML = Cells(RowNdx, ColNdx).text
I can only read 1024 bytes - not nearly enough. Is there something else I can do to read the cell into a string variable?
View 4 Replies
View Related
Nov 2, 2006
I want to use the Camera facility to show a 13 column by 19 row section of a large report in another WorkBook. I have outlined the area I need copied .... clicked the Camera icon .....but when I enter the data in the receiving sheet I keep getting the message ...." The Picture is too large and will be truncated" Is there a maximum size that can be transferred
View 2 Replies
View Related
Mar 27, 2014
I often end up in a scenario where I want to be able to move text from one column into another using a formula, which involves pasting a formula down a filtered range... E.g. Column A contains both ID numbers & dates, I want to move ID numbers into Column B, so I apply a filter to all ID numbers using a formula "=A1" and paste it down the filtered range.
It becomes an issue when there is greater than 50 - 60 thousand rows - excel throws an error "selection range is too complex" and disregards the filtered range.
Only thing I can think of would be a VBA script to automate pasting a formula in blocks of 50,000 rows.
View 6 Replies
View Related
Feb 4, 2014
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Code:
Dim row As Long
Dim refname
row = 3
Do While Cells(row).Value ""
[Code] .....
View 3 Replies
View Related
May 8, 2014
Excel 2007-2010. I'm using match(string, range,0) but there must be a limitation on the length of the string since I know the string is in the range but it returns #value as if it is not found. Is there a VBA solution to get around this without having to loop/cycle through the entire range?
View 4 Replies
View Related
Jan 21, 2014
Excel 2010. There is a limit to the usability of the
=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
View 7 Replies
View Related
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
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
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
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
Aug 3, 2009
Exit Sub alternative. I have the following
View 5 Replies
View Related
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
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
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
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
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
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
