I have several different categories of data, with each list named after the full category title. The data is stored on another worksheet. On the worksheet that will be interacted with, there is a dropdown menu that allows you to choose which category of data you want.
I am trying to get a macro that produces a dropdown menu the next column over of all the items in that category. Here is the code that I have so far.
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
I have 2 dropdown lists.
The 1st shows the portfolio list and the 2nd one should display project names based on the portfolio selection in Dropdown1.
My data resides in another sheet where Column B is the Portfolio list,Column C project list and Column D to X some data related to the project.
I have to give cell link reference to the project selected in dropdown 2 so that the other values in the dashboard changes based on a vlookup formula.
I have attached the sample sheet for reference with some dump values.
Dashboard sample.xlsx
Using Excel 2013.
I have two sheets in a workbook.
Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result
Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1
Mod 2
Mod 3
Mod 4
Text 1
0
0
0
0
Text 2
2
0.5
0.25
0.25
Text 3
7
1.75
0.875
0.875
What I want to happen is that the Result field value on table one is the result of:
Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)
For example:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
I have the following:
One workbook containing 6 worksheets. The first worksheet, let's call it "Alpha", allows a user to enter a name, date, tkt#, issue, etc.
The second third and fourth sheets parse this information out by using formulas and references, no problem there.
The sheet I'm wanting to manipulate is next, "Summary".
Summary has a column that contains "issue counts" which is pulled via formula from the other pages. That part works, with no problem.
What I want to be able to do is to trigger a macro called "Mail_with_outlook" once any cell in the "issue counts" column exceeds a given value (for testing purposes, 8).
The catch seems to be that I want this macro to run when the user enters the information on the "Alpha" sheet, meaning that the "Summary" sheet will not be the active sheet.
I've attempted utilizing the "worksheet_change" and "worksheet_calculate" methods but have apparently wondered down the wrong path without a light it seems... I'd appreciate any assistance.
="=ROUNDUP("&"50.42"&",0)"
I take this paste text only to get:
=ROUNDUP(50.42,0)
this wont work normally until i open the cell and press enter.
Any way I could do that for more than 1 cell at a time?
I have created List box called ListBox10.
Under format control I have put the range of A1:A3.
Cell A1 is text: Order 1
cell A2 is text: Order 2
cell A3 is Text: Order 3
Im trying to trigger different macros from each of the options available in the List Box...here is what I have so far.
Sub ListBox10_Click()
If ListBox10.Text = "Order 1" Then
Application.Run ("Macro1")
If ListBox10.Text = "Order 2" Then
[Code]...
Can seem to get the thing to run getting Block If without end If error.
when I press Delete a value within a Target Range in a sheet, the sheet event is trigered.
how can I press Delete a value within the Target Range without the event being trigered ?
I have a Multi-Select Active-X Control Listbox and an associated Button. I am trying to create a dynamic Select ALL, Clear All Button. Clear All Function Works, but Select All triggers the Change Event for the Listbox.
figuring out a way to either
1. Disable the Change event until the "Select all" loop is completed or
2. Find a way to write the action for the Button directly, such that the click event is programmed as part of the Change event, and everything is done in one.
Cannot Attach file due to file size, code below:
Option Explicit
Dim i As Long
Dim j As Long
[Code].....
Is there a way to trigger a warning box (ie - must be divisible by 50) when a specific entry is selected from a pull down validation list? The triggering factor is a correct answer to the validation so I do not think the error alert will work in this instance.
View 2 Replies View RelatedIn the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View 2 Replies View RelatedI have a drop down in IE in which four values are there
I will need to select each one at at time to make some change and move to next dropdown
the dropdown in IE should ideally have 4 dropdowns 01,02,03 and 04
However due to vendor errors we may have any of the above missing from dropdown or extra orderpoints in the dropdown like 05
IE.document.getElementById("vendororderpoint").value ="01" is the code to select order point 01
I need an alert in excel if any of the 4 dropdowns is missing.
I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
I am trying to wrap my brain around dependant dropdowns and have hit a brick wall.
Using the Contextures web site [url="http://www.contextures.com/xlDataVal02.html#TwoWord"] I can get part of the problem to work but when I try to add a third dropdown i get lost. Attached is a sample of the workbook.
I have
Userform
Textbox1
Textbox2
textbox1 containing the province
textbox2 containing the city
If didn't click any value of province the city textbox2 don't have any value and if i click the one of the province the city will now have a value . Is that possible
Here is the picture : [URL] .....
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.
For example:
L-3x3x1/2
L-2x2-1/2x1/4
C6@8.2#
C8@11.5#
W12@16.0#
W21@44.0#
What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)
Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.
I have two dropdowns, both use a different source lists and both use the same reference cell to deliver the result, My problem is I need the contents of the cell to be deleted and the new result inserted. At the moment if the cell is already filled the new result is put in the next column.
What I would like to happen is to have two dropdowns one above the other (B2 and B4) and the result in a cell below them in B6, which ever dropdown is used the result will fill the cell B6 this cell is then used for vlookup.
I have this code:
Code:
Sub comments1()
With ActiveSheet
With .Rows("180:186")
.Hidden = Not .Hidden
End With
[code].....
Now, when I run this code, sometimes (but oddly not every time) the dropdowns do not reappear where they were originally placed.
Biz develop an xls tab that will have dropdowns.
Essentially, one dropdown will filter another dropdown... etc...
Would also want a DISTINCT list for all dropdowns...
I've used the DropDown's from the CONTROL toolbar and although it seems easy enough to supply a datasource, not sure how filter/distinct might work.
I have a project that has a variable number of worksheets, dependant on the number of models in the job. These worksheets usually only use a single page, but may use two pages if the model is large (not often the case though). I have a "Template1" worksheet that is copied as a whole, to equal the number of models.
To minimize the file size that is generated when copying out the "Template1" worksheet, I have put the template of the second page on a separate worksheet "Template2". Now the initial copy is only of a single page, and the second page is only copied into the new worksheet if needed.
Sub AddPg2()
Dim CrntPg As String
CrntPg = ThisWorkbook.ActiveSheet. Name
Application. ScreenUpdating = False
ThisWorkbook.Worksheets("Template2").Visible = True
Worksheets("Template2").Activate
ActiveSheet.Range("A47:T96").Select
Selection.Copy
Worksheets(CrntPg).Activate
ActiveSheet.Range("A47").Select
ActiveSheet.Paste
ActiveSheet.Range("D58").Select
ThisWorkbook.Worksheets("Template2").Visible = False
Application.ScreenUpdating = True
End Sub
If I do this process manually, I toggle DesignMode "on", and it works, but I if I record the process, toggling DesignMode does not show in the code. how to include the comboboxes and checkboxes in the copy/paste process.
I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.
I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.
I need to setup a dropdown list (I already have the dropdown created) so that once a value in the list is used, it disappears from the list. Now for the monkey wrench...It need to completely re-populate once the list that is being generated from it is cleared.
I also need to copy a value from one sheet to another sheet, however, the location needs to be matched with a corresponding value from the first sheet. Hopefully it will be easier to understand within the workbook (I feel like I'm not explaining it well).
I have attached a sample copy of the workbook and what I am trying to accomplish.
Mens_Dorm_Sample.xlsm
Dependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
I recorded the following macro which deletes specific buttons and drop downs from a sheet. I would like a macro that can delete all Buttons and Drop Downs without them being specified.
View 3 Replies View RelatedIs there a way to set up drop downs so that you can type in the drop down box and it searches for the closest match?
View 1 Replies View RelatedBased on selecting 3 parameters of respectively 2, 8 and 70 variable ("size", "from" and "to") I would like to have excel return a line of 12 columns.
All data can be listed in 1120 lines x 15 columns (whereof the data in 3 columns are selectable)
Text-based data (container sized and port names, if you're curious) and the data (transport prices and conditions) are both numeric and text-based.
I'm familiar with VLOOKUP, but this stuff looks to be a bit above me...
I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix example [URL] Drop downs example [URL]
View 4 Replies View RelatedI have a bit of code in sheet two of my document that requires user input to produce output. However I have the document set up in a vway that requires the actual input to be placed in a vell in sheet one, and the cell in sheet two is just "=cell in sheet one". This causes the input cell in sheet two to change but the output cell does not produce the new answer.
View 4 Replies View RelatedI have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?