How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)
Dec 22, 2009
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'm working on trying to maintain an inventory management sheet where i basically enter all the spare parts issued to vehicles. Now there are more than 700 parts dat form the components of a vehicle, so i decided to split this list into 12 spare groups(Gear, Engine, Body, Axle, etc etc..) and then based on wotever spare group being selected my sheet wud pull dat specific spare group list. Since i've worked with indirect function before i thought this was a piece of cake!
However when i actually sat working on getting my sheet together i realised its not gonna be dat easy! Basically my spare group lists are a work in progress and so new items would/may be added to these lists as we start working on this sheet. So i decided to make them dynamic by using the offset function. But list validation fails when i use Indirect function on dynamic ranges!
I have a question regarding dynamic validation. I have attached a sample spreadsheet to clarify if my explanation doesn't. I have a list of codes that map to a list of other codes that are not one to one mapping. I would like for the other users of this code to populate what the correct code on our side is but without having to go thru the entire list in a validation field. (if that makes since)
Using validation before in a similar instance i used indirect to grab a named range to help shorten the list for them. However in this case that would be a ton of ranges i would have to set up! I've tried Match, countif and index in an offset formula in the validation list but can never get it to work!
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges. The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.
In the data validation source field for cell#2 I have the following formula: =IF($A$1="",the_full_range,INDIRECT($A$1))
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I cant seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesnt.
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
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z 2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
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 have this code which looks information in a table. The problem is that the table is getting every time bigger.
Here the code:
VB:
With Worksheets("Sheet10").Cells(9, 3).Resize(, LastColumnf - 2) .Formula = "=IFERROR(CONCATENATE(VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,3,FALSE),""."", VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,2,FALSE)),"""")" .Value = .Value End With
How can I write $A$10:LastNewRow... Another problem rises here, LastNewRow might end up being the oldLastNewRow, i think. But maybe since it is getting bigger, there is no problem, I don't know.
Instead of having a static reference like $C$550 I would like to have a dynamic one.
I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?
I want to start a new thread about this issue - it was previously listed on an old thread but slightly different topic: refer[url].
The problem is I have lots of Dynamic Named Ranges using formulae such as the following: =OFFSET(RptPg!$F$9,0,0,MATCH("*",RptPg!$B$9:$B$200,-1),1) . These named ranges are created in the Name Manager box and the code inserted in the RefersTo: box.
The ranges seem to work well and are dynamic as required. When I click on the range selector button against the RefersTo formula fo rthe range the range is correctly highlighted.
However, the range is not selectable from the F5(GoTo) command.
This then means for certain VBA operations, the range cannot be found and returns an error (Object not defined etc)
Is there anyway I can correct this or work around this - dynamic ranges are at the heart of my large workbook and fundamental to its performance>
Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?
I've converted to a dynamic validation list which allows for a selection of data to be made available based on the selection of the two previous columns.
Unfortunately since creating the new validation a circulating error occurs when tabbing through the table (B) to create a new line or entry? Additionally I've found the following to occur also;
1. The payments and PO# columns converts a red shade colour?
2. The validation on Table B description column do not work while in excel online edit mode?
the dynamic name ranges I had created did not "adjust" in their position references when I added new name ranges later.
My workbook (see attachment) Master sheet has several name ranges that are based on their position to a key name range I set up..."Unit". Example below:
In the attached example I have a column of data on worksheet 2. Extra rows will frequently be added and removed from the column. I want to use that column of data to populate a validation drop down box for a whole column of cells on sheet 1. (Target cells coloured yellow for demonstration purposes)
I dont want the validation drop down to be full of blank rows so I set up a dynamic named range to refer to the column on Sheet2. It all works really well...but...only for cell D6 on sheet 2. It doesnt work on any of the other target cells on sheet 2 or on sheet 1. I have been looking at this for far too long and it has begun hurting my brain.
I have an worksheet that I import a csv into, each day a new csv is added to the bottom of the previous csv data. I have some code that extracts the date and month # from the cell and places them in helper columns. The code find the last used cell in the helper column and the imported data column to find the first and last row of the new day.
This part works fine. However, I assigned a variable name to the first and last variables and would like to uses these row number to define a range in order to use the range for a vlookup or find operation. This is where I get stuck. I want to use column x and row (variable from first bit of code) to column y and row (variable from first bit of code). Then use a vlookup/find whatever works to find the text I need and get the data. Tips on looping the code would be welcome as well.
I have searched for answers to the problem, on the board and web, but have not found a solution that works for me. (at least that I could get to work!!)
Below is the code as I have it
Public Sub Enter_Date()
Dim DateA As Date Dim DateB As Date Dim Cnt As Integer Dim End_of, Beg_of As Integer Dim Count As Integer Dim NumtoFill As Integer
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 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?
I am preparing a template for which I need a combo box. Becaue it is a template I can't have this combo box any specific input range. Because I won't know how long the data column (that wiill be used for input range) will be.
Also the data column will have many duplicates but the combo box needs to show uniques only.
I purchased the Excel Hacks book, and when attempting to do hack #53 with a drop down list, I can not get it to work. I do exactly what the book says to do, however, my chart and my drop down list are not linked together, and when I change a name in the drop down list, the information on the chart does not change.
I have a dynamic named range which allows me to have an automatically growing drop down list in B1 as more cities are added in column A. however, my workbook will have N/A errors to begin with before cities are added.
is there a way for my drop down list in B1 not to include the N/A errors and only the cities? of course when the next N/A error turns into a city, i would like the B1 drop down list to recognise this and include it.
I'm trying to make two identical worksheets in a workbook. I would like the second sheet reflect all the changes made in the first one. I thought I would simply use = but it doesn't work correctly if I add or delete rows. If I add a row between row 1 and 2 in the first sheet the formula in row 2 in the second worksheet changes from =sheet1!A2 to =sheet1!A3. The result is the same if I use absolute references which suprised me. I want the formula in row 2 to stay =sheet1!A2 whatever happens on the first sheet.
I don't care about formatting, just data are important.
The reason I want the same data on two worksheets is that the second sheet will contain some more data that should not be visible to everyone. I want to protect the second worksheet and require a password to unhide it.
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro. The worksheets are identical and each item is identified by a unique ID. Any ideas in exchange for a 6-pack Budweiser?
I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart. Does anyone know how to do this? Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
