I am trying to create a list, I know how to do this on the same sheet, I need to be able to have the list informaiton on a data sheet that contains other information that I need to have to populate other parts of the sheet. then I need to add this list data so that I can have drop down options so that users can pick from selected work descriptions
View 2 RepliesI am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
The below is causing me an issue as it is setting the ws value to equal nothing instead of sheet1 or sheet2 etc. I have my dimensions set above and there are no spelling mistakes.
It falls down on the
VB : Set LastRow = ws.Range("a65536").End(xlUp)
line but this seems to happen because ws is set to nothing.
VB:
For iramp2 = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iramp2) Then
Set ws = ActiveWorkbook.Sheets("Sheet" & iramp2)
End If
[Code] .....
I have an excel workbook with two sheets. Sheet 1 has a list of people names, rank, address, and phone numbers. Sheet 2 has boxes where a user can select a last name from all last names in sheet1. What I want to happen after the user selects the last name in sheet 2 is for it to automatically fill in the rest of the data (ie Rank, First Name, Address, city, Home phone number, Cell Phone number). What do I need to do in order for this to work?. Attached is a very small example of my much larger project.
View 3 Replies View RelatedI'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies View RelatedI need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies View RelatedI have a number of macro's which correspond to shift numbers 1 - 23
I would like to have a drop down list on the front sheet so that the user can select the number at the end of the shift which activates the corresponding macro
Split off from
Dependent/Linked Drop Downs In Cells
Originally Posted by Dave Hawley
Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?
Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick. Auto Merged Post Until 24 Hrs Passes;Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!
How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false.
I have A dropdown list (form control combo box) ...in that the values are jan,feb,mar,apr... property of cell link is given as C4..... now my doubt is suppose if i select mar from drop down, in sheet the cell C4 should Show Mar only.....
View 6 Replies View Relatedhow to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7
Note: I tried putting an if statement in the input range for box2 but won't accept it.
how to add drop list box or combo box in this yearly time sheet so every employee has his own record in this time sheet so when ever i select name from drop list all info changed, i did include table in sheet 1 as an example.
View 4 Replies View RelatedI'm not including my spreadsheet because it has company information however, I will try to articulate my request as best I can. I currently have an Excel spreadsheet with about 20 worksheets, I would like to be able to click on the first worksheet, create a "Drop Down box" or "Data Validation" box or whatever I need to do to be able to view information in a range of cells on ANOTHER worksheet within the same workbook.
I've tried Data Validation, Drop Down Boxes, Define name ranges, all to NO avail. I'm missing a step somewhere, because I'm still not able to view the information on the first worksheet.
I'm need to add a droplist with periods 1,2,3,4 etc... that references a cell then populates that same reference in a cell raange. Example: cell E1 contains the droplist with periods 1-4 as choices to select from, when you select period 1 it populates the dates jan 15 in cells E4:E7, if you select period 2 it populates dates jan 31, etc. how to add the droplist, how to expand the list, show the formula and how to expand it as I will have more data than this short example.
View 3 Replies View RelatedI am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedThe table on Sheet 1 is being used a record system for history of each lifting equipment we mobilized to our client for rental. The Door No. (Column A) is a name for each equipment. The Date Mobilized is the date when equipment left our premises and started working on site and Date Demobilized is the date when equipment went back to our yard.
To explain my requirement, refer to the first 7 rows indicates that the Door No. ATC0005. It was mobilized 7 times but demobilized 6 times. Meaning that the equipment is currently deployed because the last mobilization dated 01-Jul-13 doesnt have an corresponding data of Demobilization. My first requirement is to have a filter where in it will show the list of currently mobilized equipment. In that case I just go to Date Demobilized and filter it by only showing the blanks.
My second requirement is to know how to filter to only show the available equipment. I was able to analyze it this way: For example we take a look at the history of FLD0001, it has 5 Mobilization Date and 5 Demobilization Date. So i need to know a function to filter a Door No. that has the same number of Mobilization Date and Demobilization Date. My first solution is to just filter the Date Demobilized to not show blanks hence it will show all equipment that has the same number of Date Mob. and Date Demob. But my exact requirement is to have a real time report on a separate sheet (refer to Sheet 2).
Sheet 2 is report for equipment status. Under Equipment (Column A) are the list of equipment. Using the data available on Sheet 1, the cells under Client (Column B) should be filled up either "Available" or "Deployed".
Lastly, for all Deployed equipment only the Client Name should also be retrieved on the report on Sheet 2.
filter values.xlsx
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I am looking for a code to generate a combinations of x numbers taken 5 at the time. t
View 2 Replies View RelatedI have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).
There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.
The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)
Find attached an example sheet. I have one list with data... Name, Handle, Result. I want to have three lists from that data
List 1 would have top ten highest results
List 2 would have top ten lowest results
Some results will be the same so I would need each Name listed as they appear in the main list until ten is reached. List 3 would have top ten handle, but would not include anyone in list 1 or 2. This will be a shared workbook
I have a long list that is geneerated each week and I need to split them based on their category acrross a number of sheets all named by the category. These sheets don't exist at the beginning.
My list would be like:
Col A Col B
UK Dave
UK Bill
UK Ann
US Bob
AUS Sheila
AUS Bruce
I want to run a macro down the list and build sheets for each unique ColA ie UK, US and AUS and then copy the ColB values into the correct sheets so UK has Dave, Bill and Ann in colA of the sheet named UK. anyone have an example of this type of process.
This is what I'm trying to achieve: Display a list box (or any other appropriate form/active X control) to get input from the users which is usually a list of several terms. Once they've input the list, upon clicking 'OK' it should add them all to Col. B.
The above request WILL be a good solution, but if there is an advanced solution to this for eg., something like a list box with buttons, for instance: click on 'Articles' and it populates 'a,' 'an,' & 'the, click on 'Prepositions' and it populates all the pre-fed prepositions, etc. that would be PERFECT!
How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:
589162045620273342451261419316121721264271837414551
As you see the difference in the first row for this small example is 317425 and the list base on this difference is :
145121641256131742367141843478151944589162045691017214671011182247811
1219234891213202449101314212550111415222651121516232752131617242853
The maximum number is 53. doing this one time find, but I have to do it with my 2200 difference.
On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:
Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................
What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).
I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
View 9 Replies View RelatedI have been working on a project with 30 products and 20 different companies.
I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.
The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).
I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)
I have included a generic excel file to show what I am going for.
Maybe this problem is not too much complicated for some of you experts. I have this:
I have in sheet "Products", in column A, a lists of Products from row 2 to N. (Product 1 , Product 2, Product 15, Product 37, etc).
How to generate in sheet "Menu" a matrix of buttons with the label of each button taken from the value of each cell in column A of sheet1.
I mean, the buttons would have the label Product 1 , Product 2, Product 15, Product 37, etc. Each button associated with macro "My_Macro".
I would like that the buttons be organize as simetrical as possible (regarding number of rows and columns)
* I'm attaching sample file with input in sheet "Products" and output desired for those few products. The real list could have 100 Products.