If Function Limitation
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
ADVERTISEMENT
Oct 22, 2009
I know about the 7 max nested function. But I cannot seem to use VLOOKUP to complete my task. Here is what i got.
=IF(E14=1,".05%",IF(E14=2,".10%",IF(E14=3,".15%",IF(E14=4,".20%",IF(E14=5,".25%",IF(E14=6,".30%",IF( E14=7,".35%",IF(E14=8,".40%"))))))))
This formula is set in a "list" validation. So if I click drop down and select "1" I get a return of .05% obviously as the formula shows......ok, we got it!
Problem: So now I want to add IF E14=0,".0%, but it will exceed my precious "7" IF function max? Can someone PLEASE help the rookie with this formula.
View 14 Replies
View Related
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
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
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
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 28, 2006
I am trying to do an "If" statement but because of the limitation of seven nested "IF" statements.
Here is what I would like to do:
=IF($C$9="HC",'Rate Matrix'!F$5,IF($C$9=0,'Rate Matrix'!F$6,IF($C$9=1, 'Rate Matrix'!F$7, IF($C$9=2,'Rate Matrix'!F$8, IF($C$9=3,'Rate Matrix'!F$9, IF($C$9=4,'Rate Matrix'!F$10, IF($C$9=5,'Rate Matrix'!F$11, IF($C$9=6,'Rate Matrix'!F$12, IF($C$9=7,'Rate Matrix'!F$13, IF($C$9=8,'Rate Matrix'!F$14, IF($C$9=9,'Rate Matrix'!F$15, IF($C$9=10,'Rate Matrix'!F$16, IF($C$11=0,'Rate Matrix'!F$17, IF($C$9=12,'Rate Matrix'!F$18, IF($C$9=13,'Rate Matrix'!F$19, IF($C$9=14,'Rate Matrix'!F$20, IF($C$9=15,'Rate Matrix'!F$21)
View 11 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Dec 3, 2013
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
View 2 Replies
View Related
Jul 17, 2008
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View 13 Replies
View Related
Nov 25, 2008
I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
View 6 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
