Let's say I have pivot data in the form of:
| Major Category | Minor Category |
toys | balls |
toys | puzzles |
toys | blocks |
clothes | shirts |
clothes | pants |
I want to loop through every minor category and display it and its major_category on a different worksheet. (I'm doing more than this, but for simplicity sake ...
I'm trying to make a budget type spreadsheet for a club I run. I have a popup box where I click what was purchased, and now I want a drop down list of all the members of the club so that when I choose one name it is inputed beside what was purchased (I hope this makes sense !). I've managed to make a dropdown list in a dialog box but I don't know how to make it so that the name I choose is inputted into the spreadsheet.
View 2 Replies View RelatedI have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.
Then I want to group the clients, salesperson together so that the details can be hidden.
This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.
I have 3 sheets (Master,Detail1,Details2) in my workbook. In the master sheet I w'll have customer names. In the detail1 & 2 sheet2 I will have their details.
When I click on the customer name from my Master sheet it will go to corresponding sheet. I have provided link for that and it's working fine.
But my requirement is when I click the customer from Master sheet, I want to get into the detiled sheet as well as want to display the records only related to the custmor I clicked/selected in my master sheet.
I was wondering if there was a way to create a pop-up list to appear when a user selects a certain cell that would provide the user with a list of (in this particular case) inventory items. The user would select which items they require for the task at hand and the total $$cost of the items would appear in the cell once is is deselected.
View 4 Replies View RelatedI'was given a piece of code to read through stationeries in Lotues Notes so I could quickly retrieve all the Sendto, CC and BCC information.
I'm now trying to modify that so I can check two specific folders in a mailbox and retrieve all the dates that e-mails have been received on.
Code is here:-
Sub new_reader()
Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object
Sheets("Sheet4").Select
counter = 2
Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GETDATABASE("servername", "boxname")
If Maildb.IsOpen = False Then
Maildb.OPENMAIL
End If
compare one coloum with another and return a corresponding value in the adjacent cell. For example
I have 4 colums A,B,C,D. In "column A" I enter Products names (Example- Apple, Orange..)and in corresponding Coloumn B, I enter Product Codes (01 for Apple and 02 for Orange etc..) When I enter Apple in coloumn C for 10 rows (C1 thru C10), I need to get Code 01 in Column D in all rows (D1 thru D10)
[URL]
I had this posted in Formulas and functions
I have, 10 combobox, if the user makes click in the combo,start the event combobox1_change, and the value of the combobox is searching in excel, when find it, move one cell toward the cell of the left, and the value of the cell of the left is shown in a label, that work.
But I need copy teen time the same code? (My english is very bad)
this is the
Private Sub ComboBox2_Change()
If Sheets.Application <> "Materiales" Then
Sheets("Materiales").Select
End If
Range("H:H").Select
Cells.Find(What:=ComboBox2.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
I have a userform which manipulates data based on the userselection from the combo box. I have setup the userform so that the user may select up to 3 sheets due to the presence of 3 combos boxes. I need to writing an IF statement which checks to see if combobox1 is occupied to carry out a function, followed by it checking to see if combobox2 is occupied to carry out the function, and then checks to see if combobox3 is occupied and carries out the funciton.
As such, if only 1 combo box is occupied it would then only carry out the operation on combobox1's selection, and if none are occupied, nothing occurs, the box simply stays open. This is what I have so far, I know there is probably a more eligant way of writing such a If/Then/Else statement
Sub Start()
If UserForm1.ComboBox1.Value And UserForm1.ComboBox2.Value > 0 Then
Call Find1
Call kTest1
End If
If UserForm1.ComboBox3.Value And UserForm1.ComboBox4 > 0 Then
Call Find2
Call kTest2
End If................
How do I check the information from a user selection of all comboboxes on a multi-page control to format a worksheet?
A few more specifics: There are roughly forty comboboxes on a multipage with six tabs. The comboboxes contain a list of choices for how different aspects of the project are financed. I want to check for whether the user has selected a specific entry. If any of the forty comboboxes have made that selection, some code runs that formats the column of the worksheet in a specific way. I have written the code which formats the column, and it works fine, but my attempts to run the check mentioned above, have not worked. The code cannot check based on .listitem, it must check based on a specific string.
A few more clarifications: It doesn't matter whether all forty comboboxes have this selection, or one; if any of them have the selection, the code needs to recognize this. The code would currently run off a command button which performs a series of calculations, tests, and then runs the code to format the worksheet.
I have two comboboxs on a userform, both are populated like this:
(ComboBox1 is a different sheet/column)
' Sets Remarks in ComboBox4 Contents
With Worksheets("Data")
Set rng = . Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp))
End With
With ComboBox4
.RowSource = rng.Address(external:=True)
End With
The function is that ComboBox1 will populate the names on lets say Sheet1, Column A, and when selected will populate by offset all the other Textboxs, and ComboBoxs.
Textbox1 is a date
ComboBox4 is populated off of items from the data sheet
ComboBox1 is populated off of sheet1 and provides names, then fills the userform fields
In populating the Userform, it fills Combobox4.value by the offset value of the selected name.
That cell does not contain the same info that was loaded into the ComboBox initally, and it does not show it. All other ComboBox entries match preloaded values, and show.
How do I get the ComboBox to display what is in the OffSet cell value, rather than blank because its different?
Not overly familiar with ComboBoxes but what I want to do is load a ComboBox with data based upon the selection of another ComboBox
Please see attached example.
ComboBox1 - I can get to load.
ComboBox2 - I want to load but only those lines that match the above selection
TextBox - Load with the data on row selected by ComboBox2
I have a list of country names in a listbox that are populated using another worksheet that has these country names. When I make a selection in the listbox, I want the same country names to be highlighted in the worksheet. I will then be able to run a macro that takes those selected cells as input.
View 3 Replies View RelatedI have a sheet with several entries. I want to find a way to have the user go to a specific cell instantly. What I thought I'd try was a combobox that when I click on a name in the combobox. It will make the matching name from the list the active cell.
View 9 Replies View RelatedIs it possible to have a combo box in excel, where, when an item is selected, it is assigned a colour depending on which item it is? Eg, The combo box list has item 1, and item 2. If I select item1, then the text becomes red, if I select item 2, the text becomes blue. If not possible using combo box, what method can I use?
View 2 Replies View RelatedI have a dynanic range named Room on B1. My combobox1 rowsource is linked to the Dynamic range Room. I would like to be able to delete the the specified selected room from the combobox and the next 3 column C,D,E (delete Shift cells up)
View 2 Replies View RelatedMy attached files contains stock returns for companies. Each sheet contains the returns over a 5 year period for a certain stock, with the ticker symbol of the stock used as the sheet name. I want to write a sub that presents the user with a user form. This user form should have an OK and Cancel buttons, and it should have a list box with a list of all stocks. The user should be allowed to choose only one stock in the list. The sub should then display a message box that reports the average monthly return for the selected stock.
View 4 Replies View RelatedI use the code below to enter a value from a list box in a cell on a workssheet. Is it possible to code VBA to enter a number for the position of the selection in the listbox to a cell in a worksheet rather that the actual value from the list box. For example if my list is:
Option1
Option2
Option3
And I click on Option2 in the list, I can sennd the value 2 to a cell on the worksheet rather that the value "Option2' from the list.
Private Sub ListBox1_Click()
Sheets("SA").Range("SA_Poistion_To_Archive_A_New").Value = ListBox1
End Sub
I have a set of data on excel and would like to make searching for it easier.
I have created a drop down list in cell B4 (say, "Banana", "Apples" and "Orange"). Is there a way that if i select "Banana" in cell B4, excel will automatically go to cell A20 ?
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
The code I'm using that results in the following error message: "Run-Time Error '1004':
The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:
Option Explicit
Sub main_prog()
Call td_metrics_import
Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
Call create_graph
End Sub...............
I was interested in getting Excel to look up details of a person say age and put the details in another table under the correct heading.
E.g. Fred Bloggs age 25
Would look like this
First Name Surname 16-24 25 - 49
Fred Bloggs 0 Tick or something
Here is my question: For every 128 ounces of raw paint we add 20 oz's of catalyst, 71.7 oz's of acetone, 20.5 oz's of IBIB and 10.2 oz's of MBIK. So for every 1 gallon (128 oz's) of raw we add 122.4 oz's of reduction. The total mixed paint used was 4.75 gallons. I need to find a formula to breakdown the 4.75 gallons (608 oz's) to find out the raw only. The reason I need a formula for this is because every part we run has completely different reduction rates so I need to be able to change those amounts. Im pretty good with formulas in excel but this one has me floored. I understand the total raw will be around 310 ounces but I need a formula to prove this.
View 2 Replies View RelatedI have a [COLOR=blue !important][COLOR=blue !important]worksheet[/COLOR][/COLOR] containing a large amount of [COLOR=blue !important][COLOR=blue !important]data[/COLOR][/COLOR] and then another worksheet made up of summary tables. The summary table uses SUM on various fields in the data to get monthly totals.
For example one cell is
=SUM(Data!C7524,Data!C7530,Data!C7536,Data!C7542,Data!C7548,Data!C7556)
Some of the other sums can be made up of 100+ cells.
I would like to be able to keep the sum in [COLOR=blue !important][COLOR=blue !important]the [COLOR=blue !important]cell[/COLOR][/COLOR][/COLOR] but somehow get a breakdown of what that sum is made up of and the description in the adjacent column. So I could [COLOR=blue !important][COLOR=blue !important]click[/COLOR][/COLOR] on the cell and ideally it would create a new worksheet listing all the criteria values and description for that sum, looked up using the references in the sum cell.
So clicking on the one above would give me something like
Description Value
JP7600 100.00
JS0140 300.00
JS0340 100.00
JS0540 50.00
JS0740 75.00
JS2030 100.00
Is what I'm after possible?
I have a source table that contains time information about shifts people work:
E
F
G
H
37
Source Table
38
name
date
start-time
stop-time
39
John
1-1-2012
13:00
15:45
40
Frank
15-8-2012
8:45
10:30
Now I'm looking for way to 'enhance the resolution' of this information to an hourly basis.
This means I would like to know how many hours John worked on 1-1-2012 between 15:00 and 16:00 (0.75).
I probably need to add 24 calculated colums to the table corresponding with the hours of the day.
I am trying to specify all files and folders in a directory and the only help I found was the following thread: Create Index Of Files In Folder That works really well but, what I need is to specify all the folders and all the files under a directory, does any of you awsome hacks have an idea of how I can enhance the macro if the thread posted above?
View 3 Replies View RelatedI was given this new task this year. Normally this process is done by hand, by one of the managers, but since it now belongs to me, I look at it as being a little to tedious to do. So I would rather have excel do it for me.
We have about 5 CSRs who handle clients based on the first letter in the clients name. I guess to make this easier to update in the future if the macro would respond to a cell reference for the number of CSRs that would be great. I will need to perform this task Annually, and of course each time a CSR leaves or we hire another one. This way work loads are kept close to the same.
I have a list of letters in column A. Each of these letters has a corresponding number of clients in column B. I have been given the task to try to split these values equally between the 5 individuals. I know that since the numbers don’t all add up and divide equally it can’t be done easily by hand.
I have attached an excel file with what I have in the sheet. Sheet 2 shows what I would like to have as an example as output on the same sheet. (What I put on the sheet is an example of the way I want it to look, are not close to adding up to a "close to equal" total.)
I am trying to calculate the the breakdown time for each hour in the factory. I have the start time and finish time of each breakdown. There are 8 hours in a shift starting at 6:45. So hour 1 would be (6:45-7:45), hour 2 (7:45-8:45). If the machine breaks down between 7:06 and 8:20 what will be the amount of time lost in each hour? In this example it would be 39 mins in hour 1 and 35 mins in hours 2. How I can achieve this result?
View 4 Replies View RelatedHow to Create a Work Breakdown Structure (WBS) in Excel automatically with given data through macros.
In the attached excel sheet
work_breakdown_structure_.xls
Is there any way to do it for data automatically?