VBA To Get Average Of Range Of Cells Based On Named Range In Different Column

Apr 10, 2013

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.

View 1 Replies


ADVERTISEMENT

Define Range Based On Named Row And Column

Jul 26, 2006

I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right.

Sub LastRowCol()
Dim intLastRow As Integer
Dim intLastCol As Integer
Dim ws As Worksheet

For Each ws In Worksheets

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column

View 3 Replies View Related

Create Named Range Based On Column Values

Dec 30, 2007

I have a worksheet that has hourly data. So Column C of this worksheet looks like

1
1
1
1
2
2
2
3
3
3
3
3
3

What I want to do is, given an hour h, automatically create a named range that contains all the rows such that the value of Column C is equal to h.

View 9 Replies View Related

Dynamic Named Range Based On Last Date In Column

Mar 9, 2008

I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.

View 5 Replies View Related

Excel 2010 :: Put Named Range In Column Of Cells?

Jun 21, 2014

using excel 2010

I created named range selection called "Contractors".....how do I put the drop down lists in a column of cells now?

View 2 Replies View Related

Find Matched Value In A Range For Named Cell Then Copy Range Cells Below

Aug 6, 2013

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.

View 9 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

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).....

View 9 Replies View Related

Formula: Average Value Of Cells Left Of Highest N Values In Above Column Range

Sep 24, 2006

I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)

Does anybody know what this formula would look like?

Example:

-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------

? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)

View 4 Replies View Related

Correct Syntax For Function To Find Average Based On Background Color Of Cells In Range

May 20, 2014

I'm working on a workbook that will track staffing patterns.

The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".

On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] ........

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] .......

I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.

[Code] ........

How might I use the following functions to find the average of the source fields instead of the SUM?

View 7 Replies View Related

Formula To Average Range Of Cells And Copying It To Calculate Next Range Of Cells?

Mar 17, 2014

I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).

I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.

If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.

I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:

=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)

View 2 Replies View Related

Sum Range Using 1 Column Of Named Range As Criteria

Dec 21, 2006

how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range?
For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria.

Eg., Column A holds fruit names:
Apple
Orange
Banana
Apple

and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.

View 3 Replies View Related

Determining If Cell Is Part Of Named Range And What That Named Range Is?

Aug 16, 2014

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.

View 5 Replies View Related

Use Only 1 Specific Column From Named Range

Nov 28, 2013

I'm using dynamic named ranges a lot recently. One thing that bothers me is that I have to do so many for one range.

For example I have a named range from A1:G30. Now I can use this named_range to do vlookup etc. but when I for example want to use the match with the index function I have to define a new range because for the match function to use it need only one column or row. Is there some hidden command I could use like named_range_row1 ? This would make it so much easier to read the code and I dont have to construct so many named_ranges.

View 3 Replies View Related

Intersect Named Range And Column

Nov 11, 2011

I have a Worksheet Change event that when a cell (H5) is changed, it does a search for the value on another sheet. When it finds that value, I would like it to find the intersection of the column it found the value in and a named range.

Here is what I have so far. It works when I use the

Code:
.Range("H4") = c.Offset(15)
, but not the
Code:
.Range("S8") = isect.Offset(3)
.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
UpperCase Target

'Fills in 60 Point form when part number is changed
'Sheets("60 POINT").Unprotect

[Code] ........

View 9 Replies View Related

Create A Named Range In VBA For Column A, Which Is A Sum Of B And C

Feb 17, 2007

I would like to create a named range in VBA for column A, which is a sum of B and C. The problem is that A can have 0 as a value. What I would really like to do is define the named range in column A as A2 to the last column with a value in column B(B10) which would make the named range in column a A2:A10.

ABC
61555560
00
1250120050
725725
00
850850
1919
7216720016
995995....

View 2 Replies View Related

Making Named Range The Last Active Value Used Within Another Named Range?

Jul 19, 2013

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.

View 2 Replies View Related

Expand Named Range Based On Cell Value

Jun 13, 2008

I have a workbook containing two worksheets - Sheet 1 being my "data entry sheet" and Sheet 2 is a sort of summary report containing multiple named ranges (all grouped according to similar values in a cell i.e. all cells containing 1-1 is named GRP1, all cells containig 1-2 is named GRP2). Basically, my workbook looks like this:

Sheet 1
A B C D
1 Lea Female 1-1 Lea Wells
2 Leo Male 1-2 Leo Anderson

Sheet 2
A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2

Hence, when I enter the data as shown in Sheet 1, Sheet 2 should look like this:

A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1
3 Lea Wells Female 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
6 Leo Anderson Male 1-2

View 4 Replies View Related

Average Based On Date Range

May 3, 2008

Having trouble working out a macro for this...

Column B with dates and column C with values. I'd like to make another column with the cell value averages based on the date. In essence, calculating daily averages. In turn I will later be adapting it to go through the all sheets in all workbooks.

View 4 Replies View Related

Creating Column Reference From Named Range?

Apr 22, 2014

I am attempting to obtain the last non-empty row in a column of a spreadsheet by using the following formula

=SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))

This works fine.

However, I'd like to make it more dynamic and be able to obtain a usable column reference (i.e., the A:A portion of the formula) from a named range (single cell).

View 1 Replies View Related

Copy Data Over Based On Reference And Named Range?

Jul 22, 2014

I got a very technical sheet which needs your experience to work.

open the attached file below to see the workbook.

It has 3 different sheets. "RESULT", "INPUT" and "SWITCH"

On "RESULT" Cell (O11) is where the person would put a Reference.

This reference is then checked on the "INPUT" sheet column A.

From the first cell it appears in Column A, until the last cell it appears in Column A should be the Row Range (Blanks included).

But I want that range (Column A to G) to be copied to "Switch" sheet. Pasted in Cell A1 downwards.

In the "SWITCH" sheet I have already given all the GREEN Cell formated areas a name - INFO.

I want all the data in INFO to be inserted into "RESULT" sheet between row 18 and 19.

I have colour coded most of the areas for you.. And added a button from which the Macro / VBA should run when clicked.

View 1 Replies View Related

Automatically Create Named Range Based On CheckBox

Jan 20, 2008

I am quite new on this forum.

I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?

I have multi checkbox in column F,

So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.

I want to entry data in the Range H14:P? by this code

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

View 9 Replies View Related

Number Of Cells In Named Range

Dec 9, 2007

I would like to know how many entries/ cells a particular named worksheet range has.
(This named range is not a global name range, but a local one only for one worksheet!)

However, my code results in 0 always, althought the worksheet in question is defined (not nothing),
I see that the name range is defined properly (Edit -> Define) and refers to serval non-empty cells.

I would like to know how many cells in general a particular named worksheet range refers to. What do I do wrong?

Const strRngNmeCllShRawIntBlPre = "preBlRng" 'name of the named range

If Not wksTarget Is Nothing Then
intPreBl = Application.WorksheetFunction.Count(wksTarget.name & "!" & strRngNmeCllShRawIntBlPre)
MsgBox intPreBl
End If

View 6 Replies View Related

Loop Through Cells Of Named Range

Feb 6, 2008

Im having some difficulty getting my code to work. On a worksheet there are pairs of columns containing data; the leftmost columns range is named. I have a userform with several optionbuttons that are named with the corresponding range names. I can successfully populate the userform's combox with unique items from a named range that is selected with the optionbutton.

When user selects item with combobox, I want to parse that named range with the combobox value, selects the value in the corresponding row (to the right) and then populate the userform's listbox with that value. The nonworking code is given below:

Private Sub cbxFiltList_Change()
Dim fCntrl As Control
Dim TempRange As Range
Dim TestRange As Range
Dim cLoop As Range
Content_lbx.Clear
For Each fCntrl In UserForm1.Frame1.Controls
If TypeName(fCntrl) = "OptionButton" Then
'If the optionbutton = True then the make the range to be tested the same name as that of the optionbutton'..............

View 2 Replies View Related

Copy And Paste Range Of Cells Based On Number Of Cell Values In Column A

Mar 17, 2014

Please see attached sample worksheet. Column A will be generated by the user manually.

I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.

I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.

Book2.xlsx‎

View 3 Replies View Related

Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

View 4 Replies View Related

Average Of Each Group In 2 Column Range

Nov 21, 2007

I just want to say If Column B = "Team A" then add up Column A (where the individual's percentage score is) and then divide by the number of matches found (this last part is the main part I'm struggling with). I've got a column that has the team name for each row as well, what's the formula word for doing this, I though vlookup, but I may be barking up the wrong tree.

View 3 Replies View Related

Dynamic Named Range = Last Used Cell In Column; RC In Formula

Apr 28, 2009

Can you use Row & Column numbers in a Formula the way you can in VBA?

I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula
because I want to use named ranges for the Row & column entries. (And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)

Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C
(it would = 470)

Also I have an existing Named Range HeaderRowNum (it = 16)

Currently I have a LOT of formulas like:
=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))

problem is any new data must be added between Rows 17 & 470

So I would like to create dynamic new forumlas to read like:
SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )...............

View 5 Replies View Related

Display Named Range Contents Based On Text In Cell

Aug 24, 2009

I have some dynamic named ranges in sheet1, and in sheet2 I have data validation dropdown list which has the names of all the DNR's in it.

What I want it to do (in sheet3, no less) is for the user to be able to pick a named range from the dropdown list, and have a particular column in sheet3 then display that entire named range.

I have made a 'data' worksheet which is the source for the data validation in sheet2. Each item in the list identically matches the name of each of the named ranges. I was hoping to be able to use some form of =INDIRECT but alas, no such luck.

So it's almost like a copy and paste function I'm after, where:

If you pick "Schedule_From" out of the DD-list, then DNR 'Schedule_From' is what is pasted in Column B in Sheet3.

DON'T want to use a PivotTable.

View 9 Replies View Related

Populate Cells With Named Range Values

Oct 2, 2008

I want to run a macro when a cell is changed and populate Cell A12 With
the value in a named range.

The Name of that range is in cell A3

so i assumed i could do the following:

View 7 Replies View Related

Creating A Named Range And Referring To Its Cells

Nov 30, 2008

here is the snippet of code I'm using

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved