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 RepliesI 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
how to create a collapsable tree in Excel using VBA.
Note: There is restriction that I cannot use ActiveX
A small example will be very useful.
I would like to add a combobox type control that will allow multiple selections. I know that the combobox in the MS Forms Object Library only allows single selections and that the list box is the way to go to allow multiple selections, but I am trying to keep the form as small as possible. I would like to add a control similar to the one used on pivot charts, where the user clicks the dropdown box and can select multiple items. I'm hoping this control is installed with MS Office and just needs a reference to it, but I am open to installing third party controls as well.
View 7 Replies View RelatedI have a some sheets in a workbook that have collapsible columns, but I need to have the sheet protected/locked. This is for a my company's price book that goes out to distributors, so I can't have the sheet unlocked to where they can manipulate pricing. However, I need to have collapsible columns. Is it possible to have these functional while the sheet is locked?
View 10 Replies View Relatedhow 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..
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].....
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.
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].....
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..............
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 RelatedHow 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 RelatedI 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...............
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.
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
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.
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.
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.
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
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
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"))))))..........
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
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.
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.
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.
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
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.
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
I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
Example
Col A: red
Col B: red
Col C: red
Col A: red
Col B: blue
Col C: not found
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????