So instead of highlighting a range of cells and naming the range from the Name Box could you enter, for instance, $A$1:$B$25 in a cell, and name it somehow, and make this be the range?
This would be particularly useful for ranges that are continually updated.
I'd prefer to do this without VB and/or a macro, but can use it if absolutely necessary.
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 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)
I have the following formula that is supposed to grab a value from C2 and check for that value in a range of cells and if it matches it is supposed to display the corresponding value in another range of cells. What am I missing??
=IF(C2='Google Doc'!$B1122:$B1266,"'Google Doc!K1122:K1266'","Not OK")
Is there a way I can stop a range of cells from recalculating once the cells in that range have a certain value?
I'm a complete newbie when it comes to any kind of programming and so...I'd need a little bit of handholding with this.
I got a range of data on sheet2, size changes everyday (dynamic) And in sheet1. I got a range of data and the size changes everyday as well. I need to copy the range in sheet2 to sheet1. The position would be at the cell after the last data in sheet1. e.g.
sheet1 got 105 data
I need to paste data of sheet2 start of row106 in sheet1
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
View 9 Replies View RelatedIm 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 would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.
View 3 Replies View RelatedI'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies View RelatedI would like to use a excel formula to resolve this problem.
Column C is the anwser I pretend.
In the same cell it will return the name of the correct range.
Ex: Between 200 and 300 there are numbers involved that are included in Range 1 and Range 2 (see second table)
Cell
A
B
C
1
From
To
Result: (How to return this value?)
2
200
300
Range1, Range2
3
301
400
N/A
4
401
500
N/A
Table to Check Value to Return:
Cell
A
B
C
1
120
280
Range 1
2
281
300
Range 2
3
600
650
Range 3
i have a sheet with a list of products,prices quantity etc
what i want to do is if a cell has a greater value than o then copy a range of cells to another sheet in the next available row
could this be done with =IF or would it need a macro to do it.
I've been trying to figure out how to use a formula that would enable me to copy/copy & move a range of cells right one column if a certain criteria is met.
Basically, if a criteria is met, I want to 'copy' everything from a row to another row, but offset it by one column (so that what was in, let's say, a1 would be in b2).
I've been tinkering with index(), if() and offset() in various combinations but think I must be missing something.
I have a long row of text cells in a range. Starting with the 1st cell I want to insert 3 empty to the right, then repeat for the next cell that had originally butted up against the first cell until 3 cells have been inserted for each cell in the range.
View 2 Replies View RelatedI have the below given macro which does quite well at deleting the cell for a given value (Value = "_y-600_z-.jpg") in a given range. What I need for it to do is delete the cell contents if '_y-600_z-.jpg' is anywhere in the string. As an example, if the cell contains 'Handler_x-111251_y-600_z-.jpg' it will delete the cell contents.
[Code] ......
I need to obtain the max value in a column of numbers (DJ3:DJ242) with the range of the cells considered, set by a slider that produces a value between 1 and 500 in cell BD51
View 4 Replies View RelatedI need to build a calculator that sums cells. I want to be able to manually plug in the start cell and end cell into different cells then have the calculator return me a number.
Start Cell
D1
1
End Cell
D3
2
3
Calculated Value
=sum(D1:D3)
4
5
Now if I change the text in B2 from "D3" to "D5", the calculated value should change as well. Is there even a way to do this in excel?
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
How do I count the number of cells that have a value greater than 0 in a range of cells?
View 2 Replies View RelatedI'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
I have data in some of the cells within range A26:A39
These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?
I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.
I anticipate that there will be 4 non blank cells within this range.
Ideally I would have data from the nonblank cells copied and pasted to cells
A40
A41
A42
A43
I have a range of cells on multiple sheets that I am trying to get an average price per gallon for. For example, I would like to divide I43:I56 by H43:H56. The kicker is that some of the cells are blank, so that throws off my division formula.
Is there a way to make the formula work in one cell? As of now, I am using the SUM function in cell I57 and H57 for each of their respective columns and then using a simple division formula in another cell. I still have the problem, however, on some of the sheets where there is no data entered and I get the dreaded #DIV/0 due to no data being present. Is there a way around this?
I am working an Excell sheet. It have many cells with formula like sum of a range of cells etc.
I wanted to divide a range of cells with the given cell (which is inturn having a formula for sum of a range of cells.) want i wanted is to divide a range of cell values with a given cell whose value (number) is obtained through a formula.
when i do
=100*(v65)/v20 for T65 cell and copy the formula for T66-T106 cell range
it is calculating for T66=100*(v66)/v21 and T67=100*(v67)/v22 ........T106=100*(v106)/v61
what I want is all the cells T66=100*(v66)/v20 ... T106=100*(v106)/v20.
I have an excel file that contains data from bank transactions.
In column A at irregular intervals is a cell with " User Group:" It depends on how many transactions there are in a batch, could be 1 or up to say 200 as to when the next "User Group" cell is found.
After that also in column A is "Item" then "Lodgement Ref" then at the end "Batch Totals"
i.e.
User Group:
Item
Lodgement Ref
Batch Totals:
User Group:
Item
Lodgement Ref
Lodgement Ref
Lodgement Ref
Batch Totals:
I want to find each instance of "Item" then move that cell and every cell over to column CK down to and including "Batch Totals" i.e. A3401 to CK3410 to CM 3400 so it lines up with the top of the transaction block.
There are over 60,000 lines so it's worth trying to find a solution as I can then use the methodology to make other refinements in the spreadsheet.
I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:
Import Data
A B
Alex 1
Ben 2
April 2
Harry 3
Data Sets
Alex Data Range
1 2 3
Refine Help Token
Cover Safe Coin
Simple Aware Change
Ben Data Range
1 2 3
Sorry Away Candy
Happy Home Soda
Mad Neutral Water
My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.
I have value of 26 in Cell A1, for example and I want to find
whether or not that value falls with a range of cells B1:C2 (which is in another sheet) and if so return the value in D Range.
A1 B C D
26 20 30 100
How would my formula look?
My data set consists of 10,000 rows and 5,000 columns. I want to search the Used Range and replace all cells which say "NaN" and make them blank.
Any code i currently use takes way too long.
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.