Cell Range Displacement
Dec 22, 2009
I'm trying to be able to refer some kind of formula to a cell that will let me displace a range.
For example
Current Range: A1:A10
Desired Range: A2:A11
Reference cell: B1
What need to do is input numbers in B1 to make the range move. In the mentioned example, i input 1 and the range moves 1 position. And if i enter 3 the range would change to A4:A13.
My logical way to do it that did not work was:
=A(1+$B$1):A(10+$B$1)
Is there a way to do this or some similar way to displace ranges??
View 11 Replies
ADVERTISEMENT
Oct 20, 2012
I'm trying to determine the net displacement between GPS locations and am wondering how I can easily code this in Excel. As far as I know, you need to calculate the distance between Point B to Point A, Point C to Point A, Point D to Point A, and so on. The following is the formula that I would use:
A
B
C
X_Coordinate
Y_Coordinate
123456
3700000
Point A
123460
3700010
Point B
Using the following formula (or setup), this is how I would calculate net displacement:
(123460-123456)^2+(3700010-3700000)^2 = (16)^2 + (10)^2 = 256 + 100 = 356
I assume if I want net squared displacement, I would square root this value SQRT(356) = 18.867
If my formula is correct above, I wonder why Excel gives me an answer for the following net displacement formula (same as above just using Excel formula): (A3-A2)^2 + (B3-B2)^2 = 116
Using this same formula, I could incorporate SQRT in as follows: =sqrt(((A3-A2)^2) + ((B3-B2)^2)), which results in 10.77. This value is clearly different than the value above (18.867) thus I wonder if I'm just mis-calculating something in Excel.
View 4 Replies
View Related
Jun 3, 2014
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
View 6 Replies
View Related
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
Sep 24, 2013
Basically I have three sheets. MAIN, Sheet 1 and Sheet 2
Sheet 1 and 2 are in the same format
A3 down is a list of country names and then B3:I71 contains the data im interested in.
I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.
View 3 Replies
View Related
Dec 23, 2008
Trying to copy a range from Sender.xls (sheet) Lists backstage
onto Userform.xls (sheet) Behind the Scenes
When trying to copy the values within a multicell range, the destination cell range (same size) becomes blank.
View 2 Replies
View Related
Dec 3, 2013
I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).
In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70
I'm looking for a way to be able to use this typed range as the range for some chart data.
View 2 Replies
View Related
Jan 27, 2012
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
View 8 Replies
View Related
Aug 25, 2006
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?
View 4 Replies
View Related
Dec 12, 2012
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies
View Related
Feb 17, 2014
I am trying to create report of the in and outs of tools we are shipping for repair.
Currently in Tab 1
Column F - Date shipped for repair
Column G- Vendor Name
Column H- Date Returned
What I need in Tab B
Vandor - Total Sent- In Repair @ Vendor- Total Repaired - Total Unrepaired
Here are Images if it makes it a little easier to understand
The Info I currently have
image1.png
The info I required filled out based on the previous data
image2.png
View 6 Replies
View Related
Oct 1, 2008
I need to have a single cell tell me (either by returning a certain word or colour) if any cell within a range of cells (a)(all within the same column) contain the same text as another cell thats located somewhere within a different range of cells (b)(again within thesame column).
View 2 Replies
View Related
Jul 7, 2014
Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
Range("DataSummary),(??,??) doesn't work.
View 1 Replies
View Related
Oct 21, 2009
I have a workbook with two sheets. The first sheet is the data sheet.
Data Worksheet
A B C D colA colA,B colA,B,C colA,B,C,D
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR
The next sheet is where the magic happens. Separate this worksheet into two sections. The reference section (column A and B) and the entry section (column E-G). Column D is a formula that will concatenate column E-G together. In the entry section any number of combinations can be entered. In the reference section I need to take the style number and reference it back to the data sheet. Then take the possible combinations from that style (column E and greater) and see if any of those combinations exist on the other sheet in column D. If so then say added....otherwise say needed.
"....In Pricing Sheet" is where I am looking to put needed or added or something like it.
A B C D E F G
STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE
CR10600S Added 1042021 104 20 21
CR10600SX Added 1042022 104 20 22
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P Needed
Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas.
View 9 Replies
View Related
Feb 26, 2014
How do I count values from a cell range in a cell range? For example I have 2 tables:
First Table:
Members
John A
John B
John C
[Code].....
I'd like to count the number of times John A, B and C appeared in the Who asked column. for this case, the sum should be 2.
View 6 Replies
View Related
Mar 4, 2008
Is anyone able to see where i went wrong, i am tring to make a function sumproduct using dynamic ranges. i can do the same thing using arrays which is simple(below), but not using ranges, below. the range is dependent on a the number of selected choices, hence the ubound.
View 8 Replies
View Related
Oct 4, 2012
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")
View 9 Replies
View Related
Jul 2, 2012
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
View 7 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Jul 16, 2009
I would like to change the name of all cells withen xrange with a text value greater than nothink.
View 4 Replies
View Related
Sep 26, 2013
(I am using Excel version 14.0.7106 and MS Office Professional 2010.)
I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.
Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:
'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select
[Code] ..............
View 7 Replies
View Related
Jul 2, 2014
I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)
Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select
[Code]...
The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.
View 5 Replies
View Related
Feb 6, 2008
I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
View 2 Replies
View Related
Apr 14, 2013
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
View 2 Replies
View Related
Aug 1, 2014
I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.
Keyword Range.xlsx‎
View 5 Replies
View Related
Feb 26, 2009
I have many workbooks which all have a summary sheet called summary, and which are all kept in the same folder (although this folder name/filepath may change from year to year).
I want to be able to set up the column headings and then copy the same set of summary information from each book so that it can then be used for analysis. The cells to be copies derive their value from formulae, it is the value that needs to be copied.
I attach a workbook with the format needed and the source cells. The source sheet will be password protected.
I know this is a question that has been raised before and I have searched the forum but not come across a question that is close enough to work from - my VB skills are appalling.
View 14 Replies
View Related
Apr 3, 2009
I need my worksheet to automatically fill in a range of cells (from Range B*:G*) with my yellow color once the last cell in the range (G*) receives data, and automatically switch back to no color if the data in Cell G* is erased. However, if somebody merges a range, it should appear as it would by default - as if no macro exists, if that makes sense. I've attached a sample worksheet.
View 2 Replies
View Related
Dec 15, 2011
I've got a range of data in Column D approx 50,000 rows long and I need to go down this range and when theres a blank cell copy the info from the cell above. I've got some code which loops through this but I need to make sure I put "EOF and the bottom of the info to stop the loop. Is there a slicker way of writing this code?
Code:
Sub TestBlankCell()
Range("D5").Select
Do
[Code].....
View 6 Replies
View Related
Oct 2, 2012
I am using 2010 and want to select a range starting at the same cell all of the time (regardless of whether or not it has contents - so let's say B7. Starting at B7, I want the range to include all of the columns starting at B and go to the end of all of the columns. Then I want the range to include all of the rows starting at B7 and end at the last row. I will eventually copy this range and paste it on another worksheet start on a specific cell of another worksheet. (Perhaps, we can add a name to this range).
View 2 Replies
View Related
Mar 10, 2013
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
View 4 Replies
View Related
