There are 4 lists containing accounting data and within each list the data rows beneath the headers are named ranges.
The parent input list is on the Payments worksheet with headings in A3:R3 and the data rows underneath are a named range entitled Payments.
The child lists contain formulas to pull the required information from the parent Payments list. These lists are identically sized and headed with headers in A1:K1. The data rows underneath the headings are named ranges entited Debits, Credits and VAT. The worksheets on which each resides have the same names.
I am looking for a way to automatically contract / expand the child lists as each row is added / deleted from the parent Payments list, ie so each list always contains the same number of rows.
The formulas already within the child lists will then ensure that the apprpriate data is copies to them from the parent Payments list.
the named range Payments will always contain at least a single row to ensure the retention of the required formulas and validation.
Note also please that there is a macro which automatically resizes the range Payments to a single row when a command button is pressed.
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 a workbook with 20 worksheets. All tabs are identical in format and layout. On B3, I have a drop down list (this may be irrelevant, but it's to select one month of the year) using simple data validation. Now, I initially had a formula in B3 equal to B3 of the first sheet, so that if I changed the month in the first sheet it will change on all sheets, it will do it the first time without touching the drop down lists on the subsequent sheets.
However, if I changed the date on any other sheet other than the first sheet, it will erase my formula and replace with the value I selected from the drop down list.
Can you guys please help me to device a way (code or formula; open to anything) where if I select, say September, on ANY sheet, it will change all the others to that selection (September) as well?
PS: B3 is actually a merged set of cell merging cells B3:D3. I know this is important because code and merged cell don't really mix.
I have to refer to a named range in a macro, but in the beginning I don't know what size it will be. So, in the beginning myRange will always consist of a single cell, which later on will be the 1st cell of the correctly sized range. While the macro runs, the correct size of the range is calculated, and at that point the range has to be resized, say to 'n' rows by 'm' columns, with the original cell as 1st cell.
I am posting this thread as a follow up to the previously posted thread entitled "IF Function and circular Reference". The reason I am changing the title because the topic we ended up talking about was not reflecting the previous title. My problem is that I got a range of independant cells (M7:M70) and the same number of dependant cells (BA7:BA70) as can be seen in the attached file. What I am trying to do is to make solver to find the value in the independant cell (e.g. M7) that makes the value in the adjacent (i.e. same row) dependant cell (e.g. BA7) equals 0. I can use solver for one row at a time but not for the whole range of cells. The question now is how can I use solver to do this task for the whole range M7:M70 simultaneously?
I have a workbook that lists a number of customers and the products that they use. I am trying to create a lookup that will follow the following process;
1 - User enters (via dropdown selection) Customer Id
2 - User enters (via filtered dropdown based on the 1st selection) Product
3 - User enters (via filtered dropdown based on the 1st and 2nd selections) Pack Size
4 - Product Id autofills itself.
My problem is twofold, firstly I don't know how to perform the "filters" in stages 2 and 3, and secondly I want to remove duplications from all lists at the appropriate times.
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))
Fa is a code that searches, once it's found something, it then puts that one cell.value into Cells(c,r). it then moves on until the next cell found with the required contence. I need it to put that one cell, and the next 4 columns along side it starting in Cells(r.c)
The problem is, I keep getting a global error. I am sure I have done something like this before, but cannot find it, or remember how i did it.
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I am trying to print a range that falls just outside of a normal A4 size. Is there a way of scaling this range to fit the page. Withough changing the column sizes before printing(each column is diffrent size). Or will i need to load the column sizes into a array and resize for the print then back to original size from array.
Before I will hand in my proposal I have to check if my ideas are achieveable in excel. To keep it simple, the idea is to use different moving averages and the Min/Max(t-1, t-n).
For example, a buy signal is given when a fast MA crosses the slow MA from below. Or a buy-signal is given when the price on t is greater than MAX(t-1:t-n). Due to the scientific aspect I have to analyse a lot of variations of these moving averages and breakouts.
My question: How can I change the range of a formula with an other cell.
For example: Range: B1=20
MAX(A1:A20) MAX(A2:A21)
How can I program it such that when I change the value in A1, the range of the formula will change with that value?
Is there a way to add cascading lists (from data validation or form/activex controls) to my excel spreadsheet WITHOUT using named ranges? Maybe structured references?
I need to avoid the named ranges because it will cause my workbook to have duplicate named range titles which I cannot avoid.
As a general example my issue arises because I have something like this where the titles are the same but they map to slightly different data. These also have to be cascading because the titles align to another list which I do not show in the example. I also considered using pivot tables, but the issue there is that the data validation lists repeat in the same worksheet. So I would have 3 cascading lists in row1 dependent on each other, but the same 3 lists in row2 dependent on row2 but not the previous row.
how can I resize a from my active cell to a specific cell (eg. L1) and select all the cells in between? I am using this code to select the last cell after my data (active cell is in column A).
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String) Dim DVListName As String DVListName = "DV" & sListName Application.Goto Reference:=sListName With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then [valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData() Dim counter As Integer counter = Sheets("Imported Data").Range("Counter")
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A Row2 56 Row3 44 Row4 65
AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?
