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
ADVERTISEMENT
Mar 27, 2008
how to go about actually doing it,
For an old project which was a till system i had two sheets,a data sheet and till system sheet , the data sheet contained :
example:
column A - numbering of the data (to be used with V Lookup)
column B - product name
Column C - price
This would then be replicated in the next 3 cells for the next data category.
Till system then had a combo box which had a cell link on the current page and data from the data sheet and then i had a price column next to i (containing V Lookup formula) the price then changed depending on the choice in the combo box.
I want to incorporate combo box's in to this new project. If i can then get some kind of stock thing i intend on then using conditional formating to colour code stock levels to show severity of needing to order etc..
View 14 Replies
View Related
Apr 3, 2014
Im recieving an error saying that i have too many levels of nesting for my formula
Is there any way to rewrite the formula i have here?
=if(I1>99,0.5,
if(I1>89,0.45,
if(I1>79,0.4,[code].....
View 3 Replies
View Related
Oct 9, 2013
I have a report of employees' hours reported for the week, which I process payroll from.
I have sorted the spreadsheet first by employee number, and then by date. I have subtotaled each day's hours (with a formula rounding each day's hours to the nearest quarter hour).
I now need to add a subtotal of weekly hours, per employee number, and I cannot figure out how to do this.
Here is an example of what I want to do. I have highlighted the second subtotal I want to add to the spreadsheet, but have been doing the =sum(xxx) formula for each employee because I can't figure out how to subtotal again.
David L 9/30/2013 0005 360 6:00 A.M. 12:00 P.M.
David L 9/30/2013 0005 112 12:30 P.M. 2:22 P.M.
David L 9/30/2013 0005 115 2:22 P.M. 4:17 P.M.
David L 9/30/2013 0005 13 4:17 P.M. 4:30 P.M.
[Code].....
View 2 Replies
View Related
Nov 22, 2006
Right now, I've this pop up box which prompts users for passwords and user name for log in. They are two main users : Admin and User. Admin is the user which is allowed to see all sheets. While User is only restricted to user interface sheets.
The problem is for the "User" I want to restrict a view in the sheet
"ELEMENT". I wish to hide columns L to AI if "User" logs in. But if "Admin" logs in, I want no columns hidden. Everything must be visible.
Private Sub CommandButton1_Click()
Sheets("Main").Select
Dim strUser As String, strPword As String, strWs As String
Dim w As Worksheet, c As Range, r As Range
strUser = Me.TextBox1.Value
strPword = Me.TextBox2.Value
Select Case strUser
Case "User"
If strPword = "User" Then
Sheets("SMXINVENTORY").Visible = xlSheetVisible
Sheets("SMVINVENTORY").Visible = xlSheetVisible
Sheets("SMIINVENTORY").Visible = xlSheetVisible
Sheets("SMF1INVENTORY").Visible = xlSheetVisible..............
View 3 Replies
View Related
Aug 21, 2012
I have daily data that has columns for day, day of week, week end, month, year. I am trying to create a pivot chart that displays the data as a line chart day by day and as a secondary axis column chart by week.
View 1 Replies
View Related
Jun 24, 2014
How can I format a Pivot Table with 5 levels. I try to use the "Design" but it only format the first 2 levels of the Pivot Table. I need to distinguish each level with different colors. I think manually is a bit difficult.
View 6 Replies
View Related
May 29, 2009
I need to convert levels to numerical values and then: Firstly, add together two vlookup values THEN divide by 2 to get an average AND THEN see if this average AND a second, individual lookup value are above a specified another value, which may be different. IF all these criteria are set, return, "yes" if either the first or second, or both criteria are not met then "no"
Or put it another way. if lookup values A+B/2>"5" AND C>"3" then "yes", Else "no"
Lookup chart:
P11
P22
P33
P44
P55
P66
1c7
1b9
1a11...............
View 3 Replies
View Related
Sep 9, 2010
I created a PivotTable that works fine. A user can click on an option and the subcategories expand; then he can choose one of those and a new group opens, etc., going 4 or 5 levels deep in options.
What I need to do is: If he then wants to start over and choose another option in the initial level, I want all of the subcategories to reset to their original closed state (unexpanded). As it is now, when he goes back to the original choice, that is closed, but everything inside it is still open as he had selected them previously.
View 6 Replies
View Related
Feb 20, 2014
How do I enter a formula to find a dollar amount for a different range dollars? For example, if $0 - $1000 = $50 and $1001 -$2000 = $100. In reality, I would like to enter a specific dollar amount within one of the levels (column 1) and the sum (column 2) would auto fill.
$0-$1000
$50
$1001-$2000
$100
View 5 Replies
View Related
May 25, 2014
I have a 14 step Lead Generating process and I want to color code each step as a level of how hot it is by where it is in the process but I cannot get 14 different reds.
When setting it up I went to More Colors, then Custom and changed the Color model to HSL and all I got was White with White borders.
I have tried to pick 14 reds from the standard colors but the difference is not very noticeable.
View 2 Replies
View Related
Nov 9, 2008
I have a maximization problem. I have to maximize Kitchen Sets given available materials. I have 11 different Kitchen Sets and every set requires different material.I have attached an excel spreadsheet for making the problem more clear.
I have T1;T2;T3;T4;M1;M2;M3;M4;O1;O2;O3;O4;Z1;Z2;Z3;Z4 different materials. Every kitchen sets requires one material from T which is fixed (for example T2), one material from M which is fixed again (for example M3), one material from O which is fixed (for example O1) and one material from Z, which is fixed (for example Z4).To complete a set the sum of all materials used should equal to 4. I have some constrains T1+T2+T3+T4<=2 it is the same for M1+M2+M3+M4<=2, O1+O2+O3+O4<=2 and Z1+Z2+Z3+Z4<=2.
So basically my goal is to say which materials should I keep in stock in order to maximize the Kitchen Sets.
View 9 Replies
View Related
Jan 22, 2007
On opening a workbook (XL2003) I want users to enter a password which will give them read Only permissions or access to the whole book. I do not want use the "save as" option it is not suitable for my needs & here is why. I have about 10 staff who need to fill in timesheets using XL SS on a public folder on the server. The staff need full access to file & management such as myself only need to view (read only) the timesheet without the ability to change data. Using the "save as" function the staff need to input 2 passwords.
The staff (in general) are not overly computer literate so I was hoping to be able to write code so that on Workbook_Open event only one password is entered and depending on the password gives the user full access or readonly access. This will also save management having to remember different passwords for read only access to different staff timesheet files & will give crude protection to the files.
Private Sub Workbook_Open()
Dim Message, Title, Default, Password As String
Message = "Enter your password" ' Set prompt.
Title = "Password" ' Set title.
Default = " " ' Set default.
' Display message, title, and default value.
Password = InputBox(Message, Title, Default)
If Password = "test" Then
Workbook.ReadOnly = True
Else
Workbook.ReadOnly = False
End If
End Sub
Error occur on "Workbook.ReadOnly" lines . I also want to put an errorchecking code for invalid passwords. Again, I know I can use the "save as" option but that is not what I need.
View 7 Replies
View Related
Apr 9, 2007
to create an order ID so when customer is transferred from the new order to Existing orders it will automatically generate CUW001 for first order then CUW002 for the second order and so on I have added a column form Order ID so this may have affected the macros pasting location. Also one last thing could you also tell me how I could highlight stock items red if the stock levels fall below 5
i have attached the spreadsheet
View 4 Replies
View Related
Mar 19, 2008
I have two groups (outlines) of columns on the same sheet. the following line :
ActiveSheet.Outline.ShowLevels 0, 1
controls the two groups. What if I want different levels od detail for each group ?
TIA.
Daniel
View 2 Replies
View Related
Mar 19, 2008
I have been using a spreadsheet which will allow you to hide columns and rows with a button associated with that row or column. I need to adapt this spreadsheet for another purpose but dont know how to recreate this feature. I have attached a screan capture to assist with my description
View 2 Replies
View Related
Mar 31, 2009
I have the following formulas which when I try to put in excel, it says I have too many levels of nesting:
=IF(K4="","",IF(AND(Hta_1>=D4,Hba_1<=D4,Hta_1<>"",Hba_1<>""),1,IF(AND(Hta_2>D4,Hba_2<=D4,Hta_2<>"",H ba_2<>""),2,IF(AND(Hta_3>D4,Hba_3<=D4,Hta_3<>"",Hba_3<>""),3,IF(AND(Hta_4>D4,Hba_4<=D4,Hta_4<>"",Hba _4<>""),4,IF(AND(Hta_5>D4,Hba_5<=D4,Hta_5<>"",Hba_5<>""),5 ,IF(AND(Hta_6>D4,Hba_6<=D4,Hta_6<>"",Hba_6<>""),6,IF(AND(Hta_7>D4,Hba_7<=D4,Hta_7<>"",Hba_7<>""),7," No reinforcements"))))))..........
View 7 Replies
View Related
Mar 21, 2008
I had more than 7 nested if statements so I attempted to formulate the named ranged formula. My 2 ranges are as follows: LDD1=IF(MONTH(P1)=MONTH(F2),C5-F5,IF(MONTH(P1)=MONTH(G2),C5-G5,IF(MONTH(P1)=MONTH(H2),C5-H5,IF(MONTH(P1)=MONTH(I2),C5-I5,IF(MONTH(P1)=MONTH(J2),C5-J5,IF(MONTH(P1)-MONTH(K2),C5-K5,IF(MONTH(P1)=MONTH(L2),C5-L5))))))) LDD2=IF(MONTH(P1)=MONTH(L2),C5-L5,IF(MONTH(P1)=MONTH(M2),C5-M5,IF(MONTH(P1)=MONTH(N2),C5-N5,IF(MONTH(P1)=MONTH(O2),C5-O5,IF(MONTH(P1)=MONTH(P2),C5-P5,IF(MONTH(P1)-MONTH(Q2),C5-Q5))))))
Basically what it's doing is comparing the current month with the month in the column heading and calculating appropriately. However, when i input the if statement (=if(LDD1,LDD1,LDD2), i get an error saying that it's an inconsistent formula and the result that it gives is wrong, too. Problem cell is e5
View 2 Replies
View Related
Jan 11, 2009
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered.
Club is 0 to 19 points and yields an 11% discount.
Bronze is 20 to 44 points and yields an 12% discount.
Silver is 45 to 59 points and yields an 11% discount.
Gold is 60 and better and yields an 11% discount.
I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View 3 Replies
View Related
Feb 9, 2013
Each item (row) in my sheet contains a logged date in column A, and a priority Level in Column G - which is via a data validation drop down (1 to 3).
What I would like to do is create a conditional format based on what the priority level is, and whether it has expired its allowed resolution time based on the logged date.
For example a Level 1 priority requires a 1 day fix, a level 2 requires a 5 day fix and a level 3 requires a 10 day fix.
If the priority level is a 1 and the logged date is over 1 day old i need the whole row to change colour. If the priority level is a 2 and the logged date is over 5 days old I need it to change colour, and if the Priority level is a 3 and the logged date is over 10 days old I need it to change colour. The colour can be the same each time, but I just need to know how to get it to trigger.
I think I have used an IF(AND( formula in the past, but cannot remember how to do it.
View 4 Replies
View Related
Jun 25, 2008
I need advice regarding the best way to subtotal data that has been exported from SQL Reporting Services to Excel. Formulas are not exported with the worksheet and need to be re-entered. The data is contained in three levels, but second level is not always present (see attached). The Level 1 data has a "pale blue" background and desciptions start in column A. Level 2 data has a "Gray-25%" background and starts in Column B. Detail level data has no fill color and descriptions for it start in Column C. The detailed data should be subtotaled in the Level 2 line directly above it (if available). The Level 2 data should be subtotaled in the Level 1 row directly above it. If no Level 2 row exists beneath a Level 1 row then all of the detail rows below should be subtotaled in the Level 1 row.
I have looked at options for keying on the color or the column contents for triggering when to calculate the subtotals, but nothing that I have tried works. The attached sample file illustrates how the data looks after being exported from Excel. The number of detail rows may change at any time as new projects are added.
View 2 Replies
View Related
Apr 14, 2009
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies
View Related
Oct 16, 2008
I am creating a summary page that shows product levels for each org. The summary page looks something like this.
ORG 863869880
Warehouse SubWHS1WHSWHS
Part NumberQTYQTYQTY
122796
143091
147388
118494
And the data source looks like this
ORGPartSubQty
863122796WHS1420
863143091WHS115
863147388WHS185
863118494WHS160
863122796VAN033
863118494VAN021
869122796WHS180..........................
I've been trying to return the qty based on the ORG and SUB using an INDEX MATCH function but it keeps giving me a #NA error. Here's the logic behind what I'm trying to do. Return Value (QTY)of part # X if ORG = B and SUB = C (in this case WHS). Here's my Formula
=INDEX(Data!$D$2:$D$43,MATCH(Summary!B$1,IF(AND(Data!$B$2:$B$43=Summary!$A4,Data!$C$2:$C$43="WHS1"), Data!$A$2:$A$43),0))
This is an array formula so I have confirmed with CTRL+SHIFT+ENTER to designate as such.
View 2 Replies
View Related
Sep 19, 2008
I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached
Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A.
I had a go at the code and it works for Level 0 but I can't get it to put to Level 1.
Sub Test()
'Dimension Variables
Dim RowTitleEntries As Integer
Dim NameLoop As Integer
Dim IndentLevel As Integer
'Set the value of RowTitleEntries
RowTitleEntries = 6
View 3 Replies
View Related
Apr 19, 2014
I am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
View 4 Replies
View Related
Jun 20, 2013
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
View 2 Replies
View Related
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 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
