I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx
My objective is to automate the creation of a dynamic top-5 list by summing a number related to multiple entries of an item (text string). Data are added monthly and while the textual strings (imported) are constant (text1 always reads as text1) the text strings themselves may vary from "text1" to "text2", etc. What I'm trying to achieve is finding the top-5 five items and copy/paste them to a range on the same worksheet. The range into which they are pasted are source cells for DSUM criteria. The frequency with which a text string appears is not critical, but the associated count is.
View 2 Replies View RelatedI have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
is it possible to have an dynamic list, so that when I add in a new entry in to the input column, the output column will automatically change to the same thing. Also then my dropdown will realise a new entry has been inputted (uses the output list) and the list will update to show this.
View 2 Replies View RelatedI Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
I have a column with dates in each cell. I need the user to the able to pick a date from a list in the adjacent cell but it must be a date on the same weekday.eg if cell a1 is 23/10/2013 then cell b1 should display a list of Fridays in the future for the user to select from and then it should put the selected value in the cell (eg 30/10/2013).
View 1 Replies View RelatedI am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
I have dynamic number in K2. How to make numbering list whose length is based on that K2 number? I want that list to start from A2. So, in other words, if K2=3, the list should be like this:
A
empty (cause my header will be there)
1.
2.
3.
Notice that there should be period after each number too.
I have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
I would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
ive got 1 sheet,with 2 printable pages in it. 1 being the summery page and 1 being the inventory page.
what i would like to be able to do is.
on the summery bit ive got a code in A22 and a number in G22
to save me time i need to create a list in the inventory bit starting at A62
im hoping it will look somthing like this
A62 = what ever the code is ( JR269 ) in A22
A63 = same code and so on until i have 26 in a list, 26 being G22
I am trying to create a list which will summarise information from a dataset depending on two input cells in which dates are inputted by the user. I would really appreciate it if you could have a look at the file I have attached and give me some idea as to what formulae I should be using!
It is important that the position of the output list remains where it is (as ideally I want to draw graphs using the information summarised in the output list.
If your confused now when you look at the file it should make a bit more sense.
have data in the attached file arranged as follows:
"Source Data" sheet Data in columns A to L will be updated daily by pasting from another workbook. On any given day the data in columns A to L may have more or fewer rows than the day before.
"Lookup Table" sheet The aim is to return the data from the "Source Data" sheet; the selection depends on the Data Validation choice in cell O1.
I would like to be able to sort this dynamic list of data - ideally by clicking a single button - in descending order of the values in column L.
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies View RelatedI'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies View RelatedIs it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies View RelatedI have ranges that are named Math, Reading, Science. I would like to create a drop down list in validation that chooses the range based on a value in a particular cell.
View 4 Replies View RelatedEvery two weeks: During Payroll, we have a list of managers and specific employees under that manager.
The managers name is in Column A and they repeat.
Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.
This is a long tedious process as there are close to 30 managers.
1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?
2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"
My problem is i need to create a drop down box in excell, now i have a list of names,
Name 1
Name 2
Name 3
Name 4..........
I need all thos enames in a drop down list but i think i keep doing things wrong, I am doing it like this,
All names are in cells A23-A33, i have highlighted them and named them (Names) using the name box in the top left corner, i have then gone to Data, Validation, List, Then i have sourced it to "Names" and clicked ok, this is where my problem is. It Makes all the boxes for Name 1 - Name 10 Drop Down Boxes... And when i click on any of them it gives me a list of all the names, when i click on one of the Names that name appears in the drop down box but the original Name dissapears from the box completely!
Name 1
Name 2
Name 3
Name 4
Name 5..................
If a specific word is entered in a cell I want a list to show in another cell. This is better explained in an example. In cell A2 I have a validation list of names, eg. Mary, Mary Birth, John, John Birth,. . .) In cell N2 I have another validation list of places , eg. Ireland, England, Wales, USA, Scotland, . . )
In A2 if a name with Birth is chosen then I only want them to be able to choose from Ireland or England.
I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:
IBM US 3 C60 Equity
IBM US 3 P60 Equity
UKX 3 P5000 Index
UKX 3 C5000 Index
I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.
----CODE----........................
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.
List of values:
Dairy NI
Dairy SI
Sheep and Beef
Equine
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture
Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!
Its really hard to explain. See the attached.. Basically I have kind of a database of Letters and each letter has several codes associated with it, although each letter is different (a few are the same but that's irrelevant), e.g. letter E means you need to use codes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.9, and 3.10. That information is stored elsewhere. I simply want a user to be able to put that letter into this box and next to it these codes will populate.
View 4 Replies View Related