I have the following formula.
=SUMIFS('Register Sales'!$E$51:$C$2500,'Register Sales'!$C$51:,A2,'Register Sales'!$L$51:$L$2500,D2)
The range changes each month so I want to use a named cell "LastRow" where I can change the number to replace the 2500. How do I use the range name in my formula without giving me a #value# error?
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.
Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)
and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().
In my company we have many spreadsheets with sections that contain formulas, which may be overwritten with "hard coding", manually entered constants, in certain situations.
Is there a way I can detect the differences between the two types of cells? I would like to setup conditional formatting that would highlight constants in a table that would generally only contains formulas.
I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.
View 3 Replies View RelatedIs it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?
I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.
If I use functions like FIND, they look at the result of a formula, and not the formula itself.
I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.
I am attempting to write a VBA function that will add textual formatting to each cell that has content. I am just learning VBA; so far, as an example I have
Sub NewSub()
i = 1
j = 1
Do
Cells(i, j) = "'" & Range(i.j).Value & "',"
i = i + 1
Loop Until i = 40
End Sub
but, as I'm sure you'll notice, it does not work. In essence I want to wrap quotes and follow with a comma each cell that contains any content. The actual content test I haven't gotten to yet.
I have a Dynamic Named Range (Entries) that shows in my Name Manager...but is not being recognized in formulas.
The range is defined with a standard DNR formula, and selecting the code in Name Manager highlights the Dynamic Range.
[Code].....
But, when I use the NAME in a simple formula, I get an error. (#NAME?)
[Code] .....
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 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" ...
View 2 Replies View Relatedi want to do I have a list of data like this
Machine Date Value
A 1 1.1
A 2 1.2
A 3 1.3
B 1 1.1
B 2 1.2
B 3 1.3
C 1 1.1
C 2 1.2
C 3 1.3
This is imported from else were and AutoFilter by machine then number and value every time a new record is added to the list.
I want to have three charts in the sheet (one for A, one for B and one for C) and i need to be able to set the data source ranges dynamically.
I have a macro i mind that will search the first column cells one at a time. something like:
Sub marco1()
'find A limit
range("a1").select
For row=1 To 100 Step 1
If cells(row,1).value<>"A" Then
ARowFin = row-1
exit For
End If
Next row
End Sub
This will find the last "A" cell's reference by index numbers as cell(ARowFin,1) But i can't find how to then use this format to select a range.
The "range" function only uses A1 notation for multiple cell selection. How do i use index notation to set a range?
If i can work this out then i can set: r1 = that range and a second range r2 in a similar fashion, then set the data source range as a union of these ranges.
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.
I've got an Excel 2003 sheet where column E has a 6-digit code input into it. I'm able to right a function off that code that outputs if E2 (for example) equals 123456 then H2 outputs "Dog," if E2 equals 123457 then H2 outputs "Cat," and so on until I reach the max of 7 nested functions. Problem is, I have a lot more than 7 codes that I need to analyze. This Excel sheet will be used every two weeks, with lots of codes input that need to have what the code means output. I created a named formula called "part1" where: =if(sheet1!E2=123456,"Dog",if(sheet1!E2=123457,"Cat", etc. It didn't work when I had $ in front of E & 2.
All variables in "part1" work when tested where H2 = part1. I created a "part2" with more variables, checked each and it works too. My problem now is that when I have H2 set to =if(part1,part1,part2) to test all the variables, it'll work on any variable in "part2" but not in "part1."
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?
For Each Row In Range("Dev_Found") 'Loop through each row in Column C
If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then
blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error")
Exit For
End If
Next Row
I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged
Sub AutoAverage()
For x = 0 To 20
For y = 0 To 171
FirstRowRef = Workbooks("NP FT01-03 010206.xls"). Sheets("NP - FT01") _
.Range("a5").Offset(12 * y, 12 * x)
LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _
.Range("a16").Offset(12 * y, 12 * x)
Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)"
Next y
Next x
End Sub
I have been handed a workbook which has been used and amended by a number of people over about 5 years. The workbook has lots of named ranges, many of which are obviously now defunct. Others may be defunct but I'm not sure. I would like to delete all of the defunct ones.
Does anyone know a way in which I can identify the cells in which a named range is currently being used?
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,
View 9 Replies View RelatedMerge two columns into one list in excel
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).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
Formula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
I have a workbook with several sheets and named ranges like ES_Date, ES_Range, ES_Volume or MC_Date, MC_Range, MC_Volume. The 1st two letters are the name of the sheet where the named ranges are located (one is dates, another volume, etc.).
What I'm trying to do is calculate averages, extract maximum and minimum values from the the named ranges using a start and end date that are entered in cells "H6" and "I6". The formula I am using right now is:
{=AVERAGE(IF((ES_Date>=H6)*(ES_Date
On a spreadsheet, I want my formula to caculate the following: if "certain cell" = "certain name", then do this calculation, if "certain cell" = "certain name", then do this calculation....and so on.
I have a drop down list of employees, which would be "certain name" and it is in a cell, which would be "certain cell". The calculation I want to do is if it is a certain employee, I want to take the manually inputted hours in another cell and multiple it by the employee's rate of pay, which would be it's own calculation including labour burden. I have 13 employees that I want to have as part of this. So, if the first valuation comes up false, it carries on to the next and so on until it finds one that matches. I have tried IF commands, but it is too long, so I tried to split it, didn't work. Tried lookup and it didn't work. Tried named formulas, didn't work. It is entirely possible that I am totally on the wrong track or in over my head, but I know this should work.
I am really struggling with the following formula:
MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
I need to select in a sheet the cells which have name reference to see if the whole sheet is dependent , i have tried to trace the dependents and precedents but that didnt work,
View 5 Replies View RelatedI 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")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
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.
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
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.
View 1 Replies View RelatedI 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.