I have a table in a workbook with names in the left column and top row. The intersecting data fill the rows under and to the right of these named areas. I need to keep this table in its own workbook, because many sheets in other workbooks will access its info. I have linked this workbook information to a sheet in each of my accessing workbook. I call this sheet "DataLink" and it is a sheet that uses an If(Original Data Ref Cell="","",Original Data Ref Cell). This works well at bring the data into my active workbook.
My active sheet has 2 drop down lists. One for the top row of names on the DataLink Sheet, and one for the left column. I have used the Offset Function refering to cells with the countif and count functions in my Data Validation setup to make the addition of the names in the DataLink Sheet Dynamic. This also is working well.
In my Taget cell (CeIl where I will get my answers) I use the Indirect(row) indirect(column) to retrieve my intersecting data relate the the row and column. This works well as long as I establish my defined names of each column and row with Ctrl-Shift-F3 and choose (Top and Left)
Now my problem. Although I can dynamically add the names to my drop down list I cannot retrieve the data from new entries on my DataLink Sheet(remember coming into active workbook from outside source) unless each time I return to my DataLink Sheet,and highlight the entire range of cells and do a new Ctrl-Shift-F3, which then ask me if I want to replace each name one by one. I have some 700 names defined. And this is disconcerting to say the least. Is there a way to add code to automatically add the contents of a cell as a defined name. This way as my information from my original data workbook enters the DataLink Sheet in each of my Active workbooks, new names that appear in the left column and top row will be defined as have intersecting data under them.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
I 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?
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.
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.
I am trying to do a dynamic drop down. The left one is working fine, where as the right one is not. (see example) I just cannot get a complete list, even though I am using the same formula.
I've trawled the internet for an answer to this problem with no luck, but I'm sure with your immense knowledge you guys will have the answer. Basically, I have a provider list offering different types of activities and I'd like a dashboard where you can select an activity type from a drop down menu and this will display the provider details that offer that particular activity. I enclose a sample of the provider list for information.
i am SAP developer and i m working on SAP - excel integration. i am fetching data from SAP tables in to excel and in each row of the excel i want to create a dynamic list box and populate dynamic values.
i know the logic for data fetching but not logic for cretion of list box dynamically and handling events of dynamic list boxes..
I have a sheet (database) which has dymamic validation lists based on a another sheet called "look ups". The problem I have is When I choose any of the validation drop downs there are two blank entries at the bottom and the cursor is on the first one of these. I would like the cursor to be on the first entry in the validation list
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 a successful dropdown menu that removes items that have already been selected through the dropdown list, however, I need it to be able to handle IDENTICAL items. At the moment, it can only handle unique items.
I'm trying to create a Rota Planner. I have a selection of shifts that need to be selected for each day. Some shifts are repeated (meaning that there may be 4 members of staff on the same shift). As ONE of each of the 4 shifts are selected, I then want only 3 to be displayed in the dropdown menu to be selected, until all of them have been selected.
I've tried to break apart the code that provides dynamic dropdown list for UNIQUE values but I'm tearing my hair out and getting nowhere.
I have created a userform where I have a combo box and an options box. I want the combo box to reference a specific list of values. And if the check box is selected, I want the same combo box to reference a different list of values. This is the present syntax I am using. For some reason, The value of the check box does not alter the list in my macro.
Private Sub Userform_Initialize() If chkHeader.Value = True Then cmbSort1.Clear With cmbSort1 .AddItem Range("A1") .AddItem Range("B1") .AddItem Range("C1")
How do you calculate the average of the last 20 values in a dynamic list? The list is in column A and updates every day by adding a new value in column A.
I have a sheet of un-ordered data with duplicates. Looks a little like this:
Sheet2 * A B
[Code]....
What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
I have created a spreadsheet that has many dynamic ranges, one of which is a list of vendors that my company uses, named "Company". This range is in a spreadsheet labeled "Analysis Search". From this list and on another worksheet, I have created a drop down list via data validation where the user inputs the vendor, and should the vendor not be in the dynamic range, a prompt will appear and warn the user that the name of the vendor is not already in the dynamic range and to add it once the entry is complete.
What I am looking to do is for Excel to automatically add the vendor to the dynamic range instead of having the user have to input the vendor. Also, I have used VB to have the range automatically sort alphabetically, so I would like to keep that functionality if possible. Is something like this feasible in Excel?
Is it possible to have a list in a summary sheet, containing the names of all other worksheets in the workbook, which will automatically update when a worksheet is added or deleted? I use Excel 2003.
i want to create dynamic validation list in excel. The logic shd be like this. i m fetching data from SAP on the click of execute button in sheet2. at this point of time i want to create dynamic validation list in sheet2 in row "E". and data to be filled in the list will be fetched from sheet1. i have added new named range in sheet1 called"mbe"(in sheet1!A) when user will execute excel i will feel data in sheet1 cloumn A. and i have written this code in the click of execute button :
With ActiveSheet.Range("myrange") .Validation.Delete .Validation.Add xlValidAlertStop, , "=mbe"
.Validation.InCellDropdown = True .Validation.IgnoreBlank = True End With
its giving runtime error "1004" (object defined error)
I need to create a large dynamic named range for all the people in my church. From this list I would like to enter their offerings to the church in a new column that has auto-complete. Ex. If I type a "P" all of the possible names are shown. Then if I type a "A" the list gets smaller with only a those members whose names start with "Pa"
I 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 list of names and an associating number with those names. I'd like to create a dynamic top 10 list, but my problem is that I have duplicate values, here's a sample:
[Code].....
I tried to use LARGE and INDEX to create a top ten list like this:
=LARGE(Names,ROW(1:10))
=INDEX(Names,MATCH(T2,Count,0),1)
But, when the list comes out, it shows only the first entry with a duplicate result:
[Code] ....
How I can get it to grab the second "Name" with the same value as the first?
I am attempting to create a form in excel with dynamic data validation fields. What has me stumped is that I want to create multiple complex dependencies (for instance, if you select something from a drop-down list in the first field, that then gives you a specific range in the second field. Then based on what you placed in the second field it gives you a specific range in the third field.). What I can't seem to work out is the formula for making this work.
Is this impossible? Also sometimes the third field my be blank.
Here is an example of what I am trying to accomplish
I've also attached an example excel spreadsheet of how I was thinking the first page would lay out.
I work for a company that has multiple properties around my region here. We all deal with similar companies and temporary workers, and what i'd like to do is set up an online document (using google docs?) where we can all go online with our login, search the document, and even add entries, as well as comment and rate on entries already existing. I may be stretching the boundaries of what excel can do, but I really don't want to start diving into programming something.. if it can be done this seems like the easiest way to accomplish this task.
Here's a quick summary of what I'm thinking (and use the attached as a visual example)
the first page will be the only place people will really "look" at. On there it will have searchable areas to load a list of names based on criteria (name/partial name/letter, as well as maybe a drop down or check box of specific standards the company or worker deals with like lighting/audio/video/etc)... In my mind the company or worker could specialize in more than one of these areas.
once you hit the search button, it will populate a list of names based off of that search. Inside of that scrollable box will be the name of the company/worker, and some ratings (it could be stars or even a number percent) - the name itself would be clickable, then next to that would be a button to rate the company/worker, and a button to comment. If you click on rating it would pop up a box to add a percent. that percentage would add a field on another page and the total average rating is what is displayed. If you click on comment, a pop up box would come up and they would be able to add a quick comment on that company or worker. Once they click add on that it would add/append that comment onto another page in the database with the time/date stamp.
In the next section, when you click on a name from the search, that contacts information will pop up in a pre-defined format. Clicking on a different name would replace all of the information with the new contacts information sort of "dynamically" loading the content each time from a separate sheet that has all of that information. Nothing in here will be clickable, just a nice laid out viewport of the information that's been entered for that company or worker with address/phone number/rating/etc.
Then finally in the last section would be a scrollable box that would pop up all of the comments that have been added for that person with a date/time stamp for each comment.
As far as rating system.. i was beating myself up a bit about how to do that, but ultimately not many people would have access to it so I'm not worried about individual hits.. as far as I'm concerned people can rate more than once, it would just add to it and it would average those ratings.
I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record. I will have over 4000 records to sort out, so I would really like to automate this. If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet. I have attached a small example of the data and output required. To make it a little more challenging the column header name is part of the data and will need stripping out as well.
I am not sure of what is it called, but I would like to be able to start typing in a cell and it to start giving me options until there is only one.
Right now I have 9 dynamic lists with anywhere from 5-20 names per list and I have a report form with multiple data validated drop down lists each pulling from a separate dynamic list. This works ok, but as the user tabs to a cell with the drop down, they must use the mouse to initiate the drop down and then select the right data. Even if the user types the right data into the cell, it will not pass validation. Is there a way to start typing and it to keep throwing away things until only one option is available and then be able to use the tab button to enter that data into the cell and move to the next cell? Or as another option how hard would it be to activate the drop down when the cell is selected and then use the arrow keys to select the right one and tab to the next cell? I would want to be able to tab through the cell if no information was needed from that dynamic list.
I have a list of named cells (i.e. Group1, Group2, Group3, etc)
I have a form control that allows the user to select which of the groups they would like to include.
I need to create a series of formulas that average the price of all the chosen groups. So if they just want to average Group1, Group3, Group5 they could do so by simply selecting the checkboxes associated with those names.
I am struggling to be able to create a formula which will look at the list of chosen values and Average only those names. If all groups were chosen this would be easy as I would simply use the:=Average(indirect(a1),indirect(a2)...and so on. However that assumes that I always know which values are being chosen. I have a fixed list of choices but if they don't choose one of the options then the indirect statement will be referencing a null value which than either returns to me a #REF or will factor in a '0' which then throws off the average.
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.
